gears/sdk/api_database.html

   1 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "DTD/xhtml1-transitional.dtd">
   2
   3 <!--
   4 Copyright 2007, Google Inc.
   5
   6 Redistribution and use in source and binary forms, with or without
   7 modification, are permitted provided that the following conditions are met:
   8
   9  1. Redistributions of source code must retain the above copyright notice,
  10     this list of conditions and the following disclaimer.
  11  2. Redistributions in binary form must reproduce the above copyright notice,
  12     this list of conditions and the following disclaimer in the documentation
  13     and/or other materials provided with the distribution.
  14  3. Neither the name of Google Inc. nor the names of its contributors may be
  15     used to endorse or promote products derived from this software without
  16     specific prior written permission.
  17
  18 THIS SOFTWARE IS PROVIDED BY THE AUTHOR ``AS IS'' AND ANY EXPRESS OR IMPLIED
  19 WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
  20 MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO
  21 EVENT SHALL THE AUTHOR BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
  22 SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
  23 PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS;
  24 OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY,
  25 WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR
  26 OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF
  27 ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
  28 -->
  29
  30 <html>
  31   <head>
  32     <title>Database Module API</title>
  33     <link rel="stylesheet" type="text/css" href="gears.css" />
  34   </head>
  35
  36 <body>
  37
  38 <h1>Database Module API</h1>
  39
  40 <div id="pagecontent">
  41
  42 <p>The Database module provides browser-local relational data storage to
  43 your JavaScript web application. Google Gears uses the open source
  44 <a href="http://www.google.com/url?sa=D&amp;q=http://www.sqlite.org/">SQLite</a>
  45 database system.</p>
  46
  47 <h3>Contents</h3>
  48 <ol class="toc">
  49   <li><a href="#overview">Overview</a></li>
  50   <li><a href="#example">Example</a></li>
  51   <li><a href="#classes">Classes
  52     </a>
  53     <ol>
  54       <li><a href="#Database" >Database</a></li>
  55       <li><a href="#ResultSet">ResultSet</a></li>
  56       </ol>
  57           <li><a href="#directories">Location of Database Files</a> </li>
  58           <li><a href="#sqlite_changes">Local Modifications to SQLite</a> </li>
  59           <li><a href="#sqlite_fts">Full-Text Search</a> </li>
  60       </li>
  61 </ol>
  62
  63 <h2 id="overview">Overview</h2>
  64 <p>The Database module is used to persistently store an application user's data on the user's computer.  Data is stored using the same-origin security policy, meaning that a web application cannot access data outside of its domain (see <a href="security.html#model">Security</a>).</p>
  65 <p>Data is stored and retrieved by executing SQL statements.  For information on the SQL syntax supported, see the SQLite document &quot;<a href="http://www.google.com/url?sa=D&amp;q=http://www.sqlite.org/lang.html">SQL as Understood By SQLite</a>&quot;, and also <a href="#sqlite_changes">local modifications to SQLite</a>, below.  Google Gears includes SQLite's <a href="#sqlite_fts">full-text search</a> extension fts2.</p>
  66 <h4>Security considerations</h4>
  67 <p>SQL statements passed to <code>execute()</code> can and should use bind parameters (<code>?</code>) to prevent SQL injection attacks.  Read about <a href="security.html#database">database security</a> best practices on the Security page. </p>
  68 <h4>Permission from user </h4>
  69 <p>The database module stores persistent data on the user's disk.  It is important that your application request the user's explicit permission to do so, to prevent inadvertently storing data on an untrusted computer.</p>
  70
  71
  72 <h2 id="example">Example</h2>
  73
  74 <pre><code>&lt;script type="text/javascript" src="<a href='tools.html#gears_init'>gears_init.js</a>"&gt;&lt;/script&gt;
  75 &lt;script type="text/javascript"&gt;
  76 var db = google.gears.factory.create('beta.database');
  77 db.open('database-test');
  78 db.execute('create table if not exists Test' +
  79            ' (Phrase text, Timestamp int)');
  80 db.execute('insert into Test values (?, ?)', ['Monkey!', new Date().getTime()]);
  81 var rs = db.execute('select * from Test order by Timestamp desc');
  82
  83 while (rs.isValidRow()) {
  84   alert(rs.field(0) + '@' + rs.field(1));
  85   rs.next();
  86 }
  87 rs.close();
  88 &lt;/script&gt;</code></pre>
  89
  90
  91 <h2 id="classes">Classes</h2>
  92
  93 <pre><code><a href="#Database">Database class</a>
  94    void      <b>open</b>([name])
  95    ResultSet <b>execute</b>(sqlStatement, [argArray])
  96    void      <b>close</b>()
  97    readonly attribute int <b>lastInsertRowId</b>
  98 </code></pre>
  99
 100 <pre><code><a href="#ResultSet">ResultSet class</a>
 101    boolean <b>isValidRow</b>()
 102    void    <b>next</b>()
 103    void    <b>close</b>()
 104    int     <b>fieldCount</b>()
 105    string  <b>fieldName</b>(int fieldIndex)
 106    variant <b>field</b>(int fieldIndex)
 107    variant <b>fieldByName</b>(string fieldName)
 108 </code></pre>
 109
 110
 111
 112 <!------------------------------------------------------------->
 113 <!-- Database                                            -->
 114 <!------------------------------------------------------------->
 115 <br>
 116 <a name="Database"></a>
 117 <h2>Database class</h2>
 118
 119 <h3>Methods</h3>
 120 <table>
 121   <tr class="odd">
 122     <th colspan="2"><a href="#Database-open" name="Database-open" class="code">open([name])</a></th>
 123     </tr>
 124   <tr class="odd">
 125     <td width="113">Return value: </td>
 126     <td width="489" class="code">void</td>
 127     </tr>
 128   <tr class="odd">
 129     <td>Parameters:</td>
 130     <td class="odd">
 131         <code>name</code> - optional.
 132     </td>
 133   </tr>
 134   <tr class="odd">
 135     <td>Exceptions:</td>
 136     <td class="" >Throws an exception if an error occurs.<br><br>
 137       <i>Currently the name, if supplied and of length greater than zero, must consist only of visible ASCII characters excluding the following
 138         characters:<br />
 139           <br />
 140         </i><kbd>/ \ : * ? &quot; &lt; &gt; | ; ,</kbd><br />
 141           <br />
 142           <i>Otherwise, open() will throw an exception. Before finalization of the API we
 143       expect to remove this restriction.</i></td>
 144     </tr>
 145   <tr class="odd">
 146     <td>Description:</td>
 147     <td class="odd">
 148         Opens the database <code>name</code>, or an unnamed database if <code>name</code> is omitted.  <code>name</code> is local to the application's origin (see <a href="security.html#model">Security</a>).
 149   </td>
 150     </tr>
 151 </table>
 152 <table>
 153   <tr class="odd">
 154     <th colspan="2"><a href="#Database-execute" name="Database-execute" class="code">execute(sqlStatement, [argArray])</a></th>
 155   </tr>
 156   <tr class="odd">
 157     <td width="113">Return Value </td>
 158     <td width="489" class="code"><a href="#ResultSet">ResultSet</a></td>
 159   </tr>
 160   <tr class="odd">
 161     <td>Parameters</td>
 162     <td class="odd"><code>sqlStatement</code> is a string containing a SQL statement, with <code>?</code> as a placeholder for bind parameters.<br />
 163       <br />
 164       <code>argArray</code> is an optional array of bind parameters to be substituted for the placeholders.</td>
 165   </tr>
 166   <tr class="odd">
 167     <td>Exceptions</td>
 168     <td class="" >
 169       Throws an exception if the SQL statement fails to execute. See the exception object's <code>message</code> attribute for details.
 170       <br><br>
 171       <p class="note">Note: If multiple processes (including <a href="api_workerpool.html">Workers</a>) attempt to write to the
 172         database at the same time, one can fail. It is up to the application to retry in these situations.</p>
 173   </tr>
 174   <tr class="odd">
 175     <td>Description</td>
 176     <td class="odd">
 177       Substitute zero or more bind parameters from <code>argArray</code> into <code>sqlStatement</code> and execute the resulting SQL statement.  There must be exactly as many items in <code>argArray</code> as their are <code>?</code> placeholders in <code>sqlStatement</code>.  <code>argArray</code> can be omitted if there are no placeholders.  The results of executing the statement are returned in a <a href="#ResultSet">ResultSet</a>.<br/>
 178       <br/>
 179       <code>open()</code> must be called and must return successfully before calling <code>execute().</code><br/>
 180       <h4>Example: </h4><br/>
 181       <code>resultSet = db.execute (<br/>
 182       &nbsp;&nbsp;'INSERT INTO MYTABLE VALUES (?, ?, ?) WHERE id=?',<br/>
 183       &nbsp;&nbsp;[1, 2, 'three four', 5]<br/>
 184       );</code><br/>
 185       <br/>
 186       SQLite automatically quote-escapes bind parameters, so in execution the statement expands to:<br />
 187         <code class="code">'INSERT INTO MYTABLE VALUES (1, 2, "three four") WHERE id=5'<br />
 188         </code>
 189       <br />
 190       For information on  SQL  syntax, see the SQLite documentation &quot;<a href="http://www.google.com/url?sa=D&amp;q=http://www.sqlite.org/lang.html">SQL as Understood By SQLite</a>&quot;
 191         <br />
 192         </p>      </td>
 193   </tr>
 194 </table>
 195 <table>
 196   <tr class="odd">
 197     <th colspan="2"><a href="#Database-close" name="Database-close" class="code">close() </a></th>
 198   </tr>
 199   <tr class="odd">
 200     <td width="113">Return value: </td>
 201     <td width="489" class="code">void</td>
 202   </tr>
 203
 204   <tr class="odd">
 205     <td>Exceptions:</td>
 206     <td class="" >Throws an exception if an error occurs. </td>
 207   </tr>
 208   <tr class="odd">
 209     <td>Description:</td>
 210     <td class="odd">Closes the database connection, if any, currently associated with this Database
 211   instance. Calling Database.close() is not required.<br />
 212   <br />
 213   </td></tr>
 214 </table>
 215 <table>
 216   <tr class="odd">
 217     <th colspan="2"><a href="#Database-lastInsertRowId" name="Database-lastInsertRowId" class="code">lastInsertRowId</a></th>
 218   </tr>
 219   <tr class="odd">
 220     <td width="113">Return value: </td>
 221     <td width="489" class="code">readonly attribute int</td>
 222   </tr>
 223
 224   <tr class="odd">
 225     <td>Description:</td>
 226     <td class="odd">Represents the <span class="code">rowid</span> of the most recent insert on this Database instance.<br />
 227       Returns zero if no
 228       inserts have ever occurred on this instance.<br />
 229       <br />
 230       Note that
 231       all rows in every table have a <span class="code">rowid</span>, even rows in tables that do not have
 232       integer-type primary keys. Thus, every successful insert updates this
 233       attribute.</td>
 234   </tr>
 235 </table>
 236
 237
 238 <p>&nbsp;</p>
 239
 240 <a name="ResultSet"></a>
 241 <h2>ResultSet class</h2>
 242
 243 <p class="backtotop"><a href="#top">Back to top</a></p>
 244 <p>
 245  A <code>ResultSet</code> is returned from a successful call to <code>Database.execute()</code>.  It contains the results of executing the SQL statement.</p>
 246 <p>A <code>ResultSet</code> is immutable, subsequent changes to the
 247   underlying database do not affect the contents. </p>
 248 <p>Iterate over the rows of the result set using <code>isValidRow()</code>, <code>next()</code>, and <code>close()</code>, calling data extraction methods for valid rows.  For example:
 249 </p>
 250 <pre><code>while (rs.isValidRow()) {
 251   console.log(rs.fieldName(0) + " == " + rs.field(0));
 252   rs.next();
 253 }
 254 rs.close();</code></pre>
 255
 256 <h3>Methods</h3>
 257
 258 <table>
 259   <tr class="odd">
 260     <th colspan="2"><a href="#ResultSet-isValidRow" name="ResultSet-isValidRow" class="code">isValidRow()</a></th>
 261   </tr>
 262   <tr class="odd">
 263     <td width="113">Return value: </td>
 264     <td width="489" class="code">boolean</td>
 265   </tr>
 266
 267   <tr class="odd">
 268     <td>Description:</td>
 269     <td class="odd">Returns true if you can call <a href="#extraction">data extraction methods</a>.</td>
 270   </tr>
 271 </table>
 272
 273 <table>
 274   <tr class="odd">
 275     <th colspan="2"><a href="#ResultSet-next" name="ResultSet-next" class="code">next()</a></th>
 276   </tr>
 277   <tr class="odd">
 278     <td width="113">Return value: </td>
 279     <td width="489" class="code">void</td>
 280   </tr>
 281   <tr class="odd">
 282     <td>Description:</td>
 283     <td class="odd">Advances to the next row of the results.</td>
 284   </tr>
 285 </table>
 286
 287 <table>
 288   <tr class="odd">
 289     <th colspan="2"><a href="#ResultSet-close" name="ResultSet-close" class="code">close()</a></th>
 290   </tr>
 291   <tr class="odd">
 292     <td width="113">Return value: </td>
 293     <td width="489" class="code">void</td>
 294   </tr>
 295   <tr class="odd">
 296     <td>Exceptions:</td>
 297     <td class="odd">Throws an exception if an error occurs.</td>
 298   </tr>
 299   <tr class="odd">
 300     <td>Description:</td>
 301     <td class="odd">Releases the state associated with this result set<br/>
 302         <br />
 303       You are required to call close() when you are finished with any result set.<br/>
 304       <br/>
 305       <em>Note: there is currently a feature request to have close called automatically when the result set goes out of scope.</em>
 306     </td>
 307   </tr>
 308 </table>
 309 <p>&nbsp;</p>
 310
 311
 312 <a name="extraction" id="extraction"></a>
 313 <h3>Methods for extracting data</h3>
 314
 315 <table>
 316   <tr class="odd">
 317     <th colspan="2"><a href="#ResultSet-fieldCount" name="ResultSet-fieldCount" class="code">fieldCount()</a></th>
 318   </tr>
 319   <tr class="odd">
 320     <td width="113">Return value: </td>
 321     <td width="489" class="code">int</td>
 322   </tr>
 323
 324   <tr class="odd">
 325     <td>Description:</td>
 326     <td class="odd">Returns the <i>number</i> of fields in this result set.</td>
 327   </tr>
 328 </table>
 329
 330 <table>
 331   <tr class="odd">
 332     <th colspan="2"><a href="#ResultSet-fieldName" name="ResultSet-fieldName" class="code">fieldName(int fieldIndex)</a></th>
 333   </tr>
 334   <tr class="odd">
 335     <td width="113">Return value: </td>
 336     <td width="489" class="code">string</td>
 337   </tr>
 338   <tr class="odd">
 339     <td>Exceptions:</td>
 340     <td class="" >Throws an exception if <code>fieldIndex</code> is out of range.</td>
 341   </tr>
 342   <tr class="odd">
 343     <td>Parameters:</td>
 344     <td class="odd"><code>fieldIndex</code> : the zero-based index of the desired field </td>
 345   </tr>
 346   <tr class="odd">
 347     <td>Description:</td>
 348     <td class="odd">Returns the <i>name</i> of the specified field in the
 349       current result set.  This name is derived from the SQL statement which was executed.</td>
 350   </tr>
 351 </table>
 352
 353 <table>
 354   <tr class="odd">
 355     <th colspan="2"><a href="#ResultSet-field" name="ResultSet-field" class="code">field(int fieldIndex)</a></th>
 356   </tr>
 357   <tr class="odd">
 358     <td width="113">Return value: </td>
 359     <td width="489" class="code">variant</td>
 360   </tr>
 361   <tr class="odd">
 362     <td>Exceptions:</td>
 363     <td class="" >Throws an exception if <code>fieldIndex</code> is out of range.</td>
 364   </tr>
 365   <tr class="odd">
 366     <td>Parameters:</td>
 367     <td class="odd"><code>fieldIndex</code> : the zero-based index of the desired field </td>
 368   </tr>
 369   <tr class="odd">
 370     <td>Description:</td>
 371     <td class="odd">Returns the <i>contents</i> of the specified  field in the
 372       current row.</td>
 373   </tr>
 374 </table>
 375 <table>
 376   <tr class="odd">
 377     <th colspan="2"><a href="#ResultSet-fieldByName" name="ResultSet-fieldByName" class="code">fieldByName(string fieldName)</a></th>
 378   </tr>
 379   <tr class="odd">
 380     <td width="113">Return value: </td>
 381     <td width="489" class="code">variant</td>
 382   </tr>
 383   <tr class="odd">
 384     <td>Exceptions:</td>
 385     <td class="" >Throws an exception if <code>fieldName</code> names a nonexistent field.</td>
 386   </tr>
 387   <tr class="odd">
 388     <td>Parameters:</td>
 389     <td class="odd"><code>fieldName</code>: the name of the desired field </td>
 390   </tr>
 391   <tr class="odd">
 392     <td>Description:</td>
 393     <td class="odd">Returns the <i>contents</i> of the specified  field in the
 394       current row.</td>
 395   </tr>
 396 </table>
 397 <a name="directories" id="directories"></a><br />
 398 <br />
 399
 400 <h2>Location of Database File </h2>
 401 <p>Database files that your application creates are stored on the user's computer in a location that is determined by the browser being used and the platform. </p>
 402 <p><strong>Windows Vista - Internet Explorer </strong></p>
 403 <p> Location: {FOLDERID_LocalAppDataLow}\Google\Google Gears for Internet Explorer<br />
 404   Example: C:\Users\Bob\AppData\LocalLow\Google\Google Gears for Internet Explorer</p>
 405 <p><strong>Windows Vista - Firefox </strong>- Database files are stored in the user local profile directory. </p>
 406 <p>Location: C:\Users\&lt;username&gt;\AppData\Local\Mozilla\Firefox\Profiles\{profile}.default\Google Gears for Firefox <br />
 407   Example:  C:\Users\Bob\AppData\Local\Mozilla\Firefox\Profiles\uelib44s.default\Google Gears for Firefox  </p>
 408 <p><strong>Windows XP - Internet Explorer </strong>- Database files are stored in the user local profile directory. </p>
 409 <p> Location: C:\Documents and Settings\&lt;username&gt;\Local Settings\Application Data\Google\Google Gears for Internet Explorer<br />
 410   Example: C:\Documents and Settings\Bob\Local Settings\Application Data\Google\Google Gears for Internet Explorer</p>
 411 <p><strong> Windows XP - Firefox </strong>- Database files are stored in the user local profile directory. </p>
 412 <p>Location: C:\Documents and Settings\&lt;username&gt;\Local Settings\Application Data\Mozilla\Firefox\Profiles\{profile}\Google Gears for Firefox<br />
 413   Example: C:\Documents and Settings\Bob\Local Settings\Application Data\Mozilla\Firefox\Profiles\uelib44s.default\Google Gears for Firefox</p>
 414 <p><strong>Mac OS/X - Firefox </strong>- Database files are stored in the user local profile directory. </p>
 415 <p>Location: Users/&lt;username&gt;/Library/Caches/Firefox/Profiles/{profile}.default/Google Gears for Firefox <br />
 416   Example: Users/Bob/Library/Caches/Firefox/Profiles/08ywpi3q.default/Google Gears for Firefox</p>
 417 <p><strong>Linux - Firefox </strong>- Database files are stored in the user home directory.</p>
 418 <p>Location: ~<em>bob</em>/.mozilla/firefox/&lt;firefox's profile id&gt;/Google Gears for Firefox <br />
 419   Example: ~bob/.mozilla/firefox/08ywpi3q.default/Google Gears for Firefox </p>
 420
 421 <a name="sqlite_changes" id="sqlite_changes"></a><br />
 422 <br />
 423
 424 <h2>Local Modifications to SQLite</h2>
 425 <p>This section describes  modifications that Google Gears makes to SQLite. </p>
 426 <h4>Attached databases</h4>
 427 <p>The SQLite <a href="http://www.sqlite.org/lang_attach.html">ATTACH
 428 and DETACH</a> commands can be used to open an arbitrary SQLite
 429 database on the user's disk.  For this reason, these commands have
 430 been disabled in Google Gears.  In the future this functionality may
 431 be exposed in a way which respects the same-origin security
 432 policy.</p>
 433
 434 <h4>PRAGMA settings</h4>
 435
 436 <p>The SQLite <a href="http://www.sqlite.org/pragma.html">PRAGMA</a>
 437 command allows setting and inspection of various platform settings,
 438 including certain settings which could potentially be used to
 439 compromise security.  At this time, Google Gears disables PRAGMA,
 440 though it is possible that specific PRAGMA uses may be re-enabled or
 441 exposed in the Database interface.</p>
 442
 443 <p>The default PRAGMA settings for Google Gears which differ from the
 444 SQLite defaults:
 445 <ul>
 446   <li>PRAGMA encoding = 'UTF-8';<br/>
 447       This controls the encoding used to store textual data on disk.
 448       UTF-16 encoding on disk is almost never a win on desktop
 449       machines, it is generally faster to read the smaller amount of
 450       UTF-8 data and decode it on the fly.
 451
 452   <li>PRAGMA auto_vacuum = 1;<br/>
 453       Over time database files can become filled with gaps where data
 454       has been deleted.  These gaps can be recovered with the VACUUM
 455       command, but VACUUM can lock the database for extended periods
 456       of time, making it a challenge to integrate into interactive
 457       applications.  auto_vacuum mode recovers these gaps
 458       incrementally as they are generated.
 459
 460   <li>PRAGMA page_size = 4096;<br/>
 461       Desktop operating systems mostly have default virtual memory and
 462       disk block sizes of 4k and higher.
 463
 464   <li>PRAGMA cache_size = 2048;<br/>
 465       Provides 8M of page cache.
 466
 467   <li>PRAGMA synchronous = NORMAL;<br/>
 468       Synchronous controls whether data is synchronized to disk before
 469       COMMIT commands return (commands not in transactions are
 470       implicitly wrapped in one).  Setting synchronous to OFF can
 471       provide a significant performance boost, at the expense of
 472       potential data corruption.  Much of the benefit of turning
 473       synchronous off can generally be achieved by using a combination
 474       of large transactions and WorkerPool.
 475
 476 </ul>
 477
 478 <a name="sqlite_fts" id="sqlite_fts"></a><br />
 479 <br />
 480
 481 <h2>Full-Text Search</h2>
 482
 483 <p>Google Gears includes an SQLite extension called <a
 484 href="http://www.sqlite.org/cvstrac/wiki?p=FtsTwo">fts2</a>, for
 485 "Full-Text Search".  fts2 allows you to create a table and search for words in TEXT data.
 486 An fts2 table is created as follows:</p>
 487
 488 <pre><code>db.execute('CREATE VIRTUAL TABLE recipe USING fts2(dish, ingredients)');</code></pre>
 489
 490 <p>This creates an fts2 table named 'recipe', with fields 'dish' and
 491 'ingredients'.  All fts2 fields are of type TEXT.  Data in the table
 492 is manipulated using standard SQL commands such as INSERT, UPDATE, and
 493 DELETE.  Like other SQLite tables, the fts2 table has an implicit
 494 unique rowid field, which acts as a unique index.  </p>
 495 <p>fts2 tables have some unique properties:</p>
 496 <ul>
 497   <li> No indices other
 498     than the rowid and full-text indices are allowed.</li>
 499   <li> fts2 tables contain a special field with the same
 500     name as the table. This field is used in search queries, as described below. </li>
 501   <li>Because the special field exists,  is important to always list the specific fields being
 502     inserted in INSERT statements, and list specific result fields in
 503     SELECT statements.  <em>SELECT * will throw exceptions when run on an fts2
 504     table.</em></li>
 505 </ul>
 506 <h3>Full-text Query Syntax </h3>
 507 <p>To query using the full-text index, use the MATCH operator as
 508 follows:</p>
 509
 510 <pre><code>&lt;table_or_field_name&gt; MATCH &lt;query&gt;</code></pre>
 511
 512 <ul>
 513   <li>If <code>&lt;table_or_field_name&gt;</code> is the name of the
 514     table, then the match is done against all fields of the table.</li>
 515         <li>If
 516     <code>&lt;table_or_field_name&gt;</code> is the name of a field, then
 517     the match is done   against that field.</li>
 518         </ul>
 519 <p>&nbsp;</p>
 520 <ul>
 521   The following
 522   returns the names of all recipes which include 'tomatoes' in any
 523   field:
 524   </li>
 525 </ul>
 526 <pre><code>var rs = db.execute('SELECT dish FROM recipe WHERE recipe MATCH ?', ['tomatoes']);</code></pre>
 527
 528 <p>Examples of  fts2 query syntax:</p>
 529 <dl>
 530 <dt>cheese tomatoes</td>
 531 <dd>Find rows containing 'cheese' and 'tomatoes' in any field.</dd>
 532 <dt>dish:stew tomatoes</td>
 533 <dd>Find rows with 'stew' in the <code>dish</code> field, and 'tomatoes' in any field.</dd>
 534 <dt>cheese OR tomatoes</td>
 535 <dd>Find rows containing either 'cheese' or 'tomatoes' in any field.  Note that OR operator must be capitalized.</dd>
 536 <dt>"green tomatoes"</td>
 537 <dd>Find rows where 'green' is immediately followed by 'tomatoes', in any single field.</dd>
 538 <dt>cheese -tomatoes</td>
 539 <dd>Find rows containing 'cheese' in any field, and not containing 'tomatoes' in any field.</dd>
 540 <dt>ch*</td>
 541 <dd>Find rows containing words which start with 'ch', including rows containing 'cheese' or 'chowder'.  The '*' must come at the end of the word.</dd>
 542 </dl>
 543
 544 <h3>Using Indices with fts2 </h3>
 545 <p>fts2 tables are restricted to contain only TEXT fields and the
 546 full-text index.  To simulate additional indices or non-TEXT fields,
 547 an auxiliary table can be used:</p>
 548
 549 <pre><code>db.execute('CREATE TABLE recipe_aux (dish TEXT PRIMARY KEY, rating INTEGER)');
 550 db.execute('CREATE VIRTUAL TABLE recipe USING fts2(dish, ingredients)');</code></pre>
 551
 552 <p>A logical row is  split across the <code>recipe</code> and
 553 <code>recipe_aux</code> tables, joined on the rowid.  The
 554 <code>recipe_aux</code> table constrains <code>dish</code> to be
 555 unique, and adds field <code>rating</code>, which will not be included
 556 in the full-text index.  For example, to search for recipes with
 557 'cheese' that have <code>rating</code> higher than 3:</p>
 558
 559 <pre><code>var rs = db.execute('SELECT recipe.rowid FROM recipe, recipe_aux ' +
 560                     ' WHERE recipe.rowid = recipe_aux.rowid AND recipe_aux.rating > ? AND recipe MATCH ?',
 561                     [3, 'cheese']);</code></pre>
 562
 563 <p>Insertions, deletions, and updates should be done within
 564 transactions to keep the tables consistent:</p>
 565
 566 <pre><code>db.execute('BEGIN');
 567 db.execute('INSERT INTO recipe_aux (dish, rating) VALUES (?, ?)', ['soup', 3]);
 568 db.execute('INSERT INTO recipe (rowid, dish, ingredients) ' +
 569            'VALUES (last_insert_rowid(), ?, ?)',
 570            ['soup', 'meat carrots celery noodles']);
 571 db.execute('COMMIT');</code></pre>
 572
 573 </div>
 574 </body>
 575 </html>