Location: PHPKode > projects > DIY Blog > diy-blog/lib/propel/docs/en/user_guide/book/chapters/ColumnTypes.html
<!--
-File         $Id: ColumnTypes.html,v 1.1 2004/07/08 01:17:06 hlellelid Exp $
-License      GNU FDL (http://www.gnu.org/copyleft/fdl.html)
-Copyright    2003, Propel project
-Author       Hans Lellelid, hide@address.com
-->
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/2000/REC-xhtml1-20000126/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
  <title>Propel Guide</title>
  <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"/>
  <link rel="Stylesheet" rev="Stylesheet" href="../css/Documentation.css" type="text/css" charset="iso-8859-1"/>
</head>
<body>

<h1>Propel Column Types </h1>

<p>In this chapter we will take a closer look at the column types available to 
  you in Propel. The column types you choose will affect both the SQL that is 
  generated for your RDBMS and in some cases the behavior of the generated OM 
  classes. The column types in Propel are mapped directly column types available 
  in Creole, the db abstraction layer used by Propel, and the Creole column types 
  are based on a simplified version of the JDBC Types. </p>
<p>The Propel generator classes translate the generic Propel column types into 
  native column types for your RDBMS, and the generated OM classes and the Propel 
  runtime framework ensure that data is properly transformed so that your calling 
  script doesn't need to concern itself with the database implementation.</p>
<h2><a name="ColumnTypes.Boolean"></a>Boolean Column Types </h2>
<p>There is a single BOOLEAN type in Propel:</p>
<ul>
  <li>BOOLEAN</li>
</ul>
<p>This type is used to represent TRUE / FALSe values. In databases that don't 
  have a BOOLEAN/BIT native datatype, an INTEGER (or SMALLINT) column will be 
  used instead (0 = false, 1 = true).</p>
<pre title="Boolean column XML declaration">&lt;table name=&quot;bool_test&quot;&gt;
  &lt;column name=&quot;boolval&quot; type=&quot;BOOLEAN&quot; defaultValue=&quot;true&quot;/&gt;
&lt;/table&gt;</pre>
<p>In PostgreSQL, for example, this will generate:</p>
<pre title="Boolean column table creation SQL">CREATE TABLE bool_test (
  boolval bit NOT NULL default 't'
);</pre>

<p>The accessor methods in the generated OM classes (e.g. <strong>getBoolval()</strong>) 
  will always return boolean values, regardless of the database implementation.</p>
<h2><a name="ColumnTypes.Numeric"></a>Numeric Column Types </h2>

<p>There are several numeric types available:</p>
<ul>
  <li>TINYINT</li>
  <li>INTEGER</li>
  <li>BIGINT</li>
  <li>DOUBLE</li>
  <li>DECIMAL</li>
  <li>FLOAT</li>
  <li>REAL</li>
</ul>
<p>You may use the <strong>size</strong> and <strong>scale</strong> attributes 
  for applicable numeric column types. Whether these are included in the generated 
  SQL files will depend on your RDBMS.</p>
<pre title="Numeric column XML declaration">&lt;table name=&quot;num_test&quot;&gt;
  &lt;column name=&quot;num&quot; type=&quot;FLOAT&quot; size=&quot;10&quot; scale=&quot;2&quot;/&gt;
&lt;/table&gt;</pre>
<p>In MySQL, for example, this will generate:</p>
<pre title="Numeric column table creation SQL">CREATE TABLE num_test (
  num FLOAT(10,2) NOT NULL
) Type=MyISAM;</pre>

<p>The numeric types only affect the generated SQL and not the generated OM classes.</p>
<p>Note that you may very well be able to store numbers with greater precision 
  in your database than you can in PHP. See the <em>precision</em> setting in 
  your <tt>php.ini</tt> file if you encounter truncation.</p>
  
  
<h2><a name="ColumnTypes.String"></a>String Column Types </h2>
  
<p>There are several string types in Propel:</p>
<ul>
  <li>CHAR</li>
  <li>VARCHAR</li>
  <li>LONGVARCHAR</li>
</ul>
<p>These columns map to the equivalents for the RDBMS. CHAR is for representing 
  fixed length strings, while VARCHAR strings can vary in size. You can specify 
  a <strong>size</strong> attribute for both of these. Generally the <strong>size</strong> 
  attribute for LONGVARCHAR will be ignored. Some databases (e.g. MySQL, PostgreSQL, 
  MS SQL Server) use a TEXT datatype for LONGVARCHAR.</p>
<p>Columns of these types will always store string values and will be returned 
  as string values. Note that the values returned will always have any trailing 
  whitespace trimmed. Some RDBMS will do this automatically (e.g. MySQL) while 
  others will not (e.g. PostgreSQL).</p>
<h2><a name="ColumnTypes.LOB"></a>LOB Column Types</h2>
<p>LOB stands for <strong>L</strong>ocator <strong>OB</strong>ject. There are 
  two LOB column types in Propel:</p>
<ul>
  <li>CLOB</li>
  <li>BLOB</li>
</ul>
<p>These datatype deserve special attention because they are handled slightly 
  differently from other datatypes in Propel. Results for LOB datatypes are returned 
  as objects of type <strong>creole.util.Blob</strong> or <strong>creole.util.Clob</strong>, 
  depending on the column type. These classes provide some useful methods (e.g. 
  for writing to file or dumping to buffer); they also provide a <strong>__toString()</strong> 
  method so that your calling code can choose to ignore the objects. <em>Note: in PHP5.0.0RC1 
  the automatic <strong>__toString()</strong> invocation for (string) cast has been disaled! For now, 
  you must explicitly invoke the <strong>__toString()</strong> methods on objects.</em></p>
<p>Take the following schema, for example:</p>
<pre title="LOB column declaration">&lt;table name=&quot;lob_test&quot;&gt;
  &lt;column name=&quot;photo&quot; type=&quot;BLOB&quot;/&gt;
&lt;/table&gt;</pre>
<p>The calling PHP code would need to cast the results of these columns to string 
  explicitly (to call the <strong>__toString() </strong>method) - <em>for now you must explicitly
  call the <strong>__toString()</strong> method in PHP.</em>:</p>
<pre title="Retrieving contents of LOB column">$lob = LobTestPeer::doSelectOne(new Criteria());
$bits = $lob-&gt;getPhoto()->__toString(); // yuk, we know; convince php devs to restore automatic __toString()</pre>
<p>... or you could call the <strong>Lob::getContents()</strong> method directly:</p>
<pre title="Explicitly retrieving LOB contents">$bits = $lob-&gt;getPhoto()-&gt;getContents();
</pre>
<p>... or you could use the utility method to save them to file:</p>
<pre title="Writing LOB contents to file">$lob-&gt;getPhoto()-&gt;writeToFile('/tmp/photo.gif');
</pre>
<p>... or you could dump the contents to buffer:</p>
<pre title="Dumping LOB contents">$lob-&gt;getPhoto()-&gt;dump();
</pre>
<h2><a name="ColumnTypes.DateTime"></a>Date &amp; Time Column Types</h2>
<p>The date and time column types in Propel are:</p>
<ul>
  <li>DATE</li>
  <li>TIME</li>
  <li>TIMESTAMP</li>
</ul>
<p>Most databases have direct equivalents for these Propel types. It's worth noting 
  that for MySQL the TIMESTAMP datatype actually maps to DATETIME. The Creole 
  layer actually transforms dates and times into the default values for your locale, 
  providing consistency across database backends.</p>
<pre title="numeric column declaration">&lt;table name=&quot;date_test&quot;&gt;
  &lt;column name=&quot;birth_date&quot; type=&quot;DATE&quot;/&gt;
  &lt;column name=&quot;birth_time&quot; type=&quot;TIME&quot;/&gt;
  &lt;column name=&quot;updated&quot; type=&quot;TIMESTAMP&quot;/&gt;
&lt;/table&gt;</pre>
<p>The following example PHP code:</p>
<pre title="numeric column declaration">print $obj-&gt;getBirthDate() . &quot;\n&quot;;
print $obj-&gt;getBirthTime() . &quot;\n&quot;;
print $obj-&gt;getUpdate() . &quot;\n&quot;;</pre>
<p>Would have the following output on a system with <strong>en_US</strong> locale:</p>
<pre title="Output on system using en_US locale">03/02/1998
12:34:23 AM
2003-12-21 18:32:01
</pre>
<p>... but the following output on a system with<strong> de_DE</strong> locale:</p>
<pre title="Output on system using de_DE locale">02.03.1998
00:34:23
2003-12-21 18:32:01</pre>
<p>Notice that using the PHP <strong>setlocale()</strong> function will change 
  the return values of accessors for DATE or TIME columns. The TIMESTAMP values 
  are formatting using ISO 8601 regardless of the locale.</p>
<h4>Custom Date Formats </h4>
<p>Importantly, you can now also pass a date format string to the accessor methods for date/time columns. You can also pass NULL to have the methods return the integer unix timestamp.</p>
<pre title="passing date formatters">print $obj-&gt;getBirthDate('n/j') . &quot;\n&quot;;
print $obj-&gt;getBirthTime('%X') . &quot;\n&quot;;
print $obj-&gt;getUpdate(null) . &quot;\n&quot;;</pre>
<p>Would have the following output on a system with <strong>en_US</strong> locale (in this example, the locale only affects the <strong>getBirthTime()</strong> call, which is using a locale-sensitive <strong>strftime()</strong>-type format string):</p>
<pre title="Output using custom format strings">3/2
12:34:23 AM
1072049521</pre>
</body>
</html>
Return current item: DIY Blog