library/adodb/docs/tips_portable_sql.htm

   1 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
   2
   3 <html>
   4 <head>
   5         <title>Tips on Writing Portable SQL for Multiple Databases for PHP</title>
   6 </head>
   7
   8 <body bgcolor=white>
   9 <table width=100% border=0><tr><td><h2>Tips on Writing Portable SQL &nbsp;</h2></td><td>
  10  <div align=right><img src="cute_icons_for_site/adodb.gif"></div></td></tr></table>
  11   <p>Updated 6 Oct 2006. Added OffsetDate example.
  12   <p>Updated 18 Sep 2003. Added Portable Native SQL section.
  13 <p>
  14
  15  If you are writing an application that is used in multiple environments and
  16   operating systems, you need to plan to support multiple databases. This article
  17   is based on my experiences with multiple database systems, stretching from 4th
  18   Dimension in my Mac days, to the databases I currently use, which are: Oracle,
  19   FoxPro, Access, MS SQL Server and MySQL. Although most of the advice here applies
  20   to using SQL with Perl, Python and other programming languages, I will focus on PHP and how
  21   the <a href="http://adodb.sourceforge.net/">ADOdb</a> database abstraction library
  22   offers some solutions.<p></p>
  23 <p>Most database vendors practice product lock-in. The best or fastest way to
  24   do things is often implemented using proprietary extensions to SQL. This makes
  25   it extremely hard to write portable SQL code that performs well under all conditions.
  26   When the first ANSI committee got together in 1984 to standardize SQL, the database
  27   vendors had such different implementations that they could only agree on the
  28   core functionality of SQL. Many important application specific requirements
  29   were not standardized, and after so many years since the ANSI effort began,
  30   it looks as if much useful database functionality will never be standardized.
  31   Even though ANSI-92 SQL has codified much more, we still have to implement portability
  32   at the application level.</p>
  33 <h3><b>Selects</b></h3>
  34 <p>The SELECT statement has been standardized to a great degree. Nearly every
  35   database supports the following:</p>
  36 <p>SELECT [cols] FROM [tables]<br>
  37   &nbsp;&nbsp;[WHERE conditions]<br>
  38   &nbsp; [GROUP BY cols]<br>
  39   &nbsp; [HAVING conditions] <br>
  40   &nbsp; [ORDER BY cols]</p>
  41 <p>But so many useful techniques can only be implemented by using proprietary
  42   extensions. For example, when writing SQL to retrieve the first 10 rows for
  43   paging, you could write...</p>
  44 <table width="80%" border="1" cellspacing="0" cellpadding="0" align="center">
  45   <tr>
  46         <td><b>Database</b></td>
  47         <td><b>SQL Syntax</b></td>
  48   </tr>
  49   <tr>
  50         <td>DB2</td>
  51         <td>select * from table fetch first 10 rows only</td>
  52   </tr>
  53   <tr>
  54         <td>Informix</td>
  55         <td>select first 10 * from table</td>
  56   </tr>
  57   <tr>
  58         <td>Microsoft SQL Server and Access</td>
  59         <td>select top 10 * from table</td>
  60   </tr>
  61   <tr>
  62         <td>MySQL and PostgreSQL</td>
  63         <td>select * from table limit 10</td>
  64   </tr>
  65   <tr>
  66         <td>Oracle 8i</td>
  67         <td>select * from (select * from table) where rownum &lt;= 10</td>
  68   </tr>
  69 </table>
  70 <p>This feature of getting a subset of data is so useful that in the PHP class
  71   library ADOdb, we have a SelectLimit( ) function that allows you to hide the
  72   implementation details within a function that will rewrite your SQL for you:</p>
  73 <pre>$connection-&gt;SelectLimit('select * from table', 10);
  74 </pre>
  75 <p><b>Selects: Fetch Modes</b></p>
  76 <p>PHP allows you to retrieve database records as arrays. You can choose to have
  77   the arrays indexed by field name or number. However different low-level PHP
  78   database drivers are inconsistent in their indexing efforts. ADOdb allows you
  79   to determine your prefered mode. You set this by setting the variable $ADODB_FETCH_MODE
  80   to either of the constants ADODB_FETCH_NUM (for numeric indexes) or ADODB_FETCH_ASSOC
  81   (using field names as an associative index).</p>
  82 <p>The default behaviour of ADOdb varies depending on the database you are using.
  83   For consistency, set the fetch mode to either ADODB_FETCH_NUM (for speed) or
  84   ADODB_FETCH_ASSOC (for convenience) at the beginning of your code. </p>
  85 <p><b>Selects: Counting Records</b></p>
  86 <p>Another problem with SELECTs is that some databases do not return the number
  87   of rows retrieved from a select statement. This is because the highest performance
  88   databases will return records to you even before the last record has been found.
  89 </p>
  90 <p>In ADOdb, RecordCount( ) returns the number of rows returned, or will emulate
  91   it by buffering the rows and returning the count after all rows have been returned.
  92   This can be disabled for performance reasons when retrieving large recordsets
  93   by setting the global variable $ADODB_COUNTRECS = false. This variable is checked
  94   every time a query is executed, so you can selectively choose which recordsets
  95   to count.</p>
  96 <p>If you prefer to set $ADODB_COUNTRECS = false, ADOdb still has the PO_RecordCount(
  97   ) function. This will return the number of rows, or if it is not found, it will
  98   return an estimate using SELECT COUNT(*):</p>
  99 <pre>$rs = $db-&gt;Execute(&quot;select * from table where state=$state&quot;);
 100 $numrows = $rs-&gt;PO_RecordCount('table', &quot;state=$state&quot;);</pre>
 101 <p><b>Selects: Locking</b> </p>
 102 <p>SELECT statements are commonly used to implement row-level locking of tables.
 103   Other databases such as Oracle, Interbase, PostgreSQL and MySQL with InnoDB
 104   do not require row-level locking because they use versioning to display data
 105   consistent with a specific point in time.</p>
 106 <p>Currently, I recommend encapsulating the row-level locking in a separate function,
 107   such as RowLock($table, $where):</p>
 108 <pre>$connection-&gt;BeginTrans( );
 109 $connection-&gt;RowLock($table, $where); </pre>
 110 <pre><font color=green># some operation</font></pre>
 111 <pre>if ($ok) $connection-&gt;CommitTrans( );
 112 else $connection-&gt;RollbackTrans( );
 113 </pre>
 114 <p><b>Selects: Outer Joins</b></p>
 115 <p>Not all databases support outer joins. Furthermore the syntax for outer joins
 116   differs dramatically between database vendors. One portable (and possibly slower)
 117   method of implementing outer joins is using UNION.</p>
 118 <p>For example, an ANSI-92 left outer join between two tables t1 and t2 could
 119   look like:</p>
 120 <pre>SELECT t1.col1, t1.col2, t2.cola <br>  FROM t1 <i>LEFT JOIN</i> t2 ON t1.col = t2.col</pre>
 121 <p>This can be emulated using:</p>
 122 <pre>SELECT t1.col1, t1.col2, t2.cola FROM t1, t2 <br>     WHERE t1.col = t2.col
 123    UNION ALL
 124 SELECT col1, col2, null FROM t1 <br>       WHERE t1.col not in (select distinct col from t2)
 125 </pre>
 126 <p>Since ADOdb 2.13, we provide some hints in the connection object as to legal
 127   join variations. This is still incomplete and sometimes depends on the database
 128   version you are using, but is useful as a general guideline:</p>
 129 <p><font face="Courier New, Courier, mono">$conn-&gt;leftOuter</font>: holds the
 130   operator used for left outer joins (eg. '*='), or false if not known or not
 131   available.<br>
 132   <font face="Courier New, Courier, mono">$conn-&gt;rightOuter</font>: holds the
 133   operator used for right outer joins (eg '=*'), or false if not known or not
 134   available.<br>
 135   <font face="Courier New, Courier, mono">$conn-&gt;ansiOuter</font>: boolean
 136   that if true means that ANSI-92 style outer joins are supported, or false if
 137   not known.</p>
 138 <h3><b>Inserts</b> </h3>
 139 <p>When you create records, you need to generate unique id's for each record.
 140   There are two common techniques: (1) auto-incrementing columns and (2) sequences.
 141 </p>
 142 <p>Auto-incrementing columns are supported by MySQL, Sybase and Microsoft Access
 143   and SQL Server. However most other databases do not support this feature. So
 144   for portability, you have little choice but to use sequences. Sequences are
 145   special functions that return a unique incrementing number every time you call
 146   it, suitable to be used as database keys. In ADOdb, we use the GenID( ) function.
 147   It has takes a parameter, the sequence name. Different tables can have different
 148   sequences. </p>
 149 <pre>$id = $connection-&gt;GenID('sequence_name');<br>$connection-&gt;Execute(&quot;insert into table (id, firstname, lastname) <br>                       values ($id, $firstname, $lastname)&quot;);</pre>
 150 <p>For databases that do not support sequences natively, ADOdb emulates sequences
 151   by creating a table for every sequence.</p>
 152 <h3><b>Binding</b></h3>
 153 <p>Binding variables in an SQL statement is another tricky feature. Binding is
 154   useful because it allows pre-compilation of SQL. When inserting multiple records
 155   into a database in a loop, binding can offer a 50% (or greater) speedup. However
 156   many databases such as Access and MySQL do not support binding natively and
 157   there is some overhead in emulating binding. Furthermore, different databases
 158   (specificly Oracle!) implement binding differently. My recommendation is to
 159   use binding if your database queries are too slow, but make sure you are using
 160   a database that supports it like Oracle. </p>
 161 <p>ADOdb supports portable Prepare/Execute with:</p>
 162 <pre>$stmt = $db-&gt;Prepare('select * from customers where custid=? and state=?');
 163 $rs = $db-&gt;Execute($stmt, array($id,'New York'));</pre>
 164 <p>Oracle uses named bind placeholders, not "?", so to support portable binding, we have Param() that generates
 165 the correct placeholder (available since ADOdb 3.92):
 166 <pre><font color="#000000">$sql = <font color="#993300">'insert into table (col1,col2) values ('</font>.$DB-&gt;Param('a').<font color="#993300">','</font>.$DB-&gt;Param('b').<font color="#993300">')'</font>;
 167 <font color="#006600"># generates 'insert into table (col1,col2) values (?,?)'
 168 # or        'insert into table (col1,col2) values (:a,:b)</font>'
 169 $stmt = $DB-&gt;Prepare($sql);
 170 $stmt = $DB-&gt;Execute($stmt,array('one','two'));
 171 </font></pre>
 172 <a name="native"></a>
 173 <h2>Portable Native SQL</h2>
 174 <p>ADOdb provides the following functions for portably generating SQL functions
 175   as strings to be merged into your SQL statements (some are only available since
 176   ADOdb 3.92): </p>
 177 <table width="75%" border="1" align=center>
 178   <tr>
 179     <td width=30%><b>Function</b></td>
 180     <td><b>Description</b></td>
 181   </tr>
 182   <tr>
 183     <td>DBDate($date)</td>
 184     <td>Pass in a UNIX timestamp or ISO date and it will convert it to a date
 185       string formatted for INSERT/UPDATE</td>
 186   </tr>
 187   <tr>
 188     <td>DBTimeStamp($date)</td>
 189     <td>Pass in a UNIX timestamp or ISO date and it will convert it to a timestamp
 190       string formatted for INSERT/UPDATE</td>
 191   </tr>
 192   <tr>
 193     <td>SQLDate($date, $fmt)</td>
 194     <td>Portably generate a date formatted using $fmt mask, for use in SELECT
 195       statements.</td>
 196   </tr>
 197   <tr>
 198     <td>OffsetDate($date, $ndays)</td>
 199     <td>Portably generate a $date offset by $ndays.</td>
 200   </tr>
 201   <tr>
 202     <td>Concat($s1, $s2, ...)</td>
 203     <td>Portably concatenate strings. Alternatively, for mssql use mssqlpo driver,
 204       which allows || operator.</td>
 205   </tr>
 206   <tr>
 207     <td>IfNull($fld, $replaceNull)</td>
 208     <td>Returns a string that is the equivalent of MySQL IFNULL or Oracle NVL.</td>
 209   </tr>
 210   <tr>
 211     <td>Param($name)</td>
 212     <td>Generates bind placeholders, using ? or named conventions as appropriate.</td>
 213   </tr>
 214   <tr><td>$db->sysDate</td><td>Property that holds the SQL function that returns today's date</td>
 215 </tr>
 216 <tr><td>$db->sysTimeStamp</td><td>Property that holds the SQL function that returns the current
 217 timestamp (date+time).
 218 </td>
 219 </tr>
 220 <tr>
 221 <td>$db->concat_operator</td><td>Property that holds the concatenation operator
 222 </td>
 223 </tr>
 224 <tr><td>$db->length</td><td>Property that holds the name of the SQL strlen function.
 225 </td></tr>
 226
 227 <tr><td>$db->upperCase</td><td>Property that holds the name of the SQL strtoupper function.
 228 </td></tr>
 229 <tr><td>$db->random</td><td>Property that holds the SQL to generate a random number between 0.00 and 1.00.
 230 </td>
 231 </tr>
 232 <tr><td>$db->substr</td><td>Property that holds the name of the SQL substring function.
 233 </td></tr>
 234 </table>
 235 <p>&nbsp; </p>
 236 <h2>DDL and Tuning</h2>
 237 There are database design tools such as ERWin or Dezign that allow you to generate data definition language commands such as ALTER TABLE or CREATE INDEX from Entity-Relationship diagrams.
 238 <p>
 239 However if you prefer to use a PHP-based table creation scheme, adodb provides you with this feature. Here is the code to generate the SQL to create a table with:
 240 <ol>
 241         <li> Auto-increment primary key 'ID', </li>
 242         <li>The person's 'NAME' VARCHAR(32) NOT NULL and defaults to '', </li>
 243         <li>The date and time of record creation 'CREATED', </li>
 244         <li> The person's 'AGE', defaulting to 0, type NUMERIC(16). </li>
 245 </ol>
 246 <p>
 247 Also create a compound index consisting of 'NAME' and 'AGE':
 248 <pre>
 249 $datadict = <strong>NewDataDictionary</strong>($connection);
 250 $flds = "
 251 <font color="#660000">  ID I AUTOINCREMENT PRIMARY,
 252   NAME C(32) DEFAULT '' NOTNULL,
 253   CREATED T DEFTIMESTAMP,
 254   AGE N(16) DEFAULT 0</font>
 255 ";
 256 $sql1 = $datadict-><strong>CreateTableSQL</strong>('tabname', $flds);
 257 $sql2 = $datadict-><strong>CreateIndexSQL</strong>('idx_name_age', 'tabname', 'NAME,AGE');
 258 </pre>
 259
 260 <h3>Data Types</h3>
 261 <p>Stick to a few data types that are available in most databases. Char, varchar
 262   and numeric/number are supported by most databases. Most other data types (including
 263   integer, boolean and float) cannot be relied on being available. I recommend
 264   using char(1) or number(1) to hold booleans. </p>
 265 <p>Different databases have different ways of representing dates and timestamps/datetime.
 266   ADOdb attempts to display all dates in ISO (YYYY-MM-DD) format. ADOdb also provides
 267   DBDate( ) and DBTimeStamp( ) to convert dates to formats that are acceptable
 268   to that database. Both functions accept Unix integer timestamps and date strings
 269   in ISO format.</p>
 270 <pre>$date1 = $connection-&gt;DBDate(time( ));<br>$date2 = $connection-&gt;DBTimeStamp('2002-02-23 13:03:33');</pre>
 271 <p>We also provide functions to convert database dates to Unix timestamps:</p>
 272 <pre>$unixts = $recordset-&gt;UnixDate('#2002-02-30#'); <font color="green"># MS Access date =gt; unix timestamp</font></pre>
 273 <p>For date calculations, we have OffsetDate which allows you to calculate dates such as <i>yesterday</i> and <i>next week</i> in a RDBMS independant fashion. For example, if we want to set a field to 6 hour from now, use:
 274 <pre>
 275 $sql = 'update table set dtimefld='.$db-&gt;OffsetDate($db-&gtsysTimeStamp, 6/24).' where ...';
 276 </pre>
 277 <p>The maximum length of a char/varchar field is also database specific. You can
 278   only assume that field lengths of up to 250 characters are supported. This is
 279   normally impractical for web based forum or content management systems. You
 280   will need to be familiar with how databases handle large objects (LOBs). ADOdb
 281   implements two functions, UpdateBlob( ) and UpdateClob( ) that allow you to
 282   update fields holding Binary Large Objects (eg. pictures) and Character Large
 283   Objects (eg. HTML articles):</p>
 284 <pre><font color=green># for oracle </font>
 285 $conn->Execute('INSERT INTO blobtable (id, blobcol) VALUES (1,empty_blob())');
 286 $conn->UpdateBlob('blobtable','blobcol',$blobvalue,'id=1');
 287
 288 <font color=green># non-oracle databases</font>
 289 $conn->Execute('INSERT INTO blobtable (id, blobcol) VALUES (1, null)');
 290 $conn->UpdateBlob('blobtable','blobcol',$blobvalue,'id=1');
 291 </pre>
 292 <p>Null handling is another area where differences can occur. This is a mine-field,
 293   because 3-value logic is tricky.
 294 <p>In general, I avoid using nulls except for dates and default all my numeric
 295   and character fields to 0 or the empty string. This maintains consistency with
 296   PHP, where empty strings and zero are treated as equivalent, and avoids SQL
 297   ambiguities when you use the ANY and EXISTS operators. However if your database
 298   has significant amounts of missing or unknown data, using nulls might be a good
 299   idea.
 300   <p>
 301   ADOdb also supports a portable <a href=http://phplens.com/adodb/reference.functions.concat.html#ifnull>IfNull</a> function, so you can define what to display
 302   if the field contains a null.
 303 <h3><b>Stored Procedures</b></h3>
 304 <p>Stored procedures are another problem area. Some databases allow recordsets
 305   to be returned in a stored procedure (Microsoft SQL Server and Sybase), and
 306   others only allow output parameters to be returned. Stored procedures sometimes
 307   need to be wrapped in special syntax. For example, Oracle requires such code
 308   to be wrapped in an anonymous block with BEGIN and END. Also internal sql operators
 309   and functions such as +, ||, TRIM( ), SUBSTR( ) or INSTR( ) vary between vendors.
 310 </p>
 311 <p>An example of how to call a stored procedure with 2 parameters and 1 return
 312   value follows:</p>
 313 <pre>   switch ($db->databaseType) {
 314         case '<font color="#993300">mssql</font>':
 315           $sql = <font color="#000000"><font color="#993333">'<font color="#993300">SP_RUNSOMETHING</font>'</font></font>; break;
 316         case '<font color="#993300">oci8</font>':
 317           $sql =
 318 <font color="#993300">    </font><font color="#000000"><font color="#993300">&quot;declare RETVAL integer;begin :RETVAL := </font><font color="#000000"><font color="#993333"><font color="#993300">SP_RUNSOMETHING</font></font></font><font color="#993300">(:myid,:group);end;&quot;;
 319 </font>   break;</font>
 320         default:
 321           die('<font color="#993300">Unsupported feature</font>');
 322         }
 323 <font color="#000000"><font color="green">      # @RETVAL = SP_RUNSOMETHING @myid,@group</font>
 324         $stmt = $db-&gt;PrepareSP($sql);        <br>    $db-&gt;Parameter($stmt,$id,'<font color="#993300">myid</font>');
 325         $db-&gt;Parameter($stmt,$group,'<font color="#993300">group</font>');
 326         <font color="green"># true indicates output parameter<br>       </font>$db-&gt;Parameter($stmt,$ret,'<font color="#993300">RETVAL</font>',true);
 327         $db-&gt;Execute($stmt); </font></pre>
 328 <p>As you can see, the ADOdb API is the same for both databases. But the stored
 329   procedure SQL syntax is quite different between databases and is not portable,
 330   so be forewarned! However sometimes you have little choice as some systems only
 331   allow data to be accessed via stored procedures. This is when the ultimate portability
 332   solution might be the only solution: <i>treating portable SQL as a localization
 333   exercise...</i></p>
 334 <h3><b>SQL as a Localization Exercise</b></h3>
 335 <p> In general to provide real portability, you will have to treat SQL coding
 336   as a localization exercise. In PHP, it has become common to define separate
 337   language files for English, Russian, Korean, etc. Similarly, I would suggest
 338   you have separate Sybase, Intebase, MySQL, etc files, and conditionally include
 339   the SQL based on the database. For example, each MySQL SQL statement would be
 340   stored in a separate variable, in a file called 'mysql-lang.inc.php'.</p>
 341 <pre>$sqlGetPassword = '<font color="#993300">select password from users where userid=%s</font>';
 342 $sqlSearchKeyword = quot;<font color="#993300">SELECT * FROM articles WHERE match (title,body) against (%s</font>)&quot;;</pre>
 343 <p>In our main PHP file:</p>
 344 <pre><font color=green># define which database to load...</font>
 345 <b>$database = '<font color="#993300">mysql</font>';
 346 include_once(&quot;<font color="#993300">$database-lang.inc.php</font>&quot;);</b>
 347
 348 $db = NewADOConnection($database);
 349 $db->PConnect(...) or die('<font color="#993300">Failed to connect to database</font>');
 350
 351 <font color=green># search for a keyword $word</font>
 352 $rs = $db-&gt;Execute(sprintf($sqlSearchKeyWord,$db-&gt;qstr($word)));</pre>
 353 <p>Note that we quote the $word variable using the qstr( ) function. This is because
 354   each database quotes strings using different conventions.</p>
 355 <p>
 356 <h3>Final Thoughts</h3>
 357 <p>The best way to ensure that you have portable SQL is to have your data tables designed using
 358 sound principles. Learn the theory of normalization and entity-relationship diagrams and model
 359 your data carefully. Understand how joins and indexes work and how they are used to tune performance.
 360 <p> Visit the following page for more references on database theory and vendors:
 361   <a href="http://php.weblogs.com/sql_tutorial">http://php.weblogs.com/sql_tutorial</a>.
 362   Also read this article on <a href=http://phplens.com/lens/php-book/optimizing-debugging-php.php>Optimizing PHP</a>.
 363 <p>
 364 <font size=1>(c) 2002-2003 John Lim.</font>
 365
 366 </body>
 367 </html>