← Back to branch summary

~ubuntu-branches/debian/sid/sqlalchemy/sid

« back to all changes in this revision

Viewing changes to doc/dialects/drizzle.html

  • Committer: Package Import Robot
  • Author(s): Piotr Ożarowski
  • Date: 2012-05-06 20:25:44 UTC
  • mfrom: (1.4.20)
  • Revision ID: package-import@ubuntu.com-20120506202544-ah8xa6gwcsqavrz5
Tags: 0.7.7-1
New upstream release

Show diffs side-by-side

added added

removed removed

Lines of Context:
doc/dialects/drizzle.html
20
20
    <script type="text/javascript">
21
21
      var DOCUMENTATION_OPTIONS = {
22
22
          URL_ROOT:    '../',
23
 
          VERSION:     '0.7.6',
 
23
          VERSION:     '0.7.7',
24
24
          COLLAPSE_MODINDEX: false,
25
25
          FILE_SUFFIX: '.html'
26
26
      };
67
67
    </div>
68
68
 
69
69
    <div id="docs-version-header">
70
 
        Release: <span class="version-num">0.7.6</span> | Release Date: March 14, 2012
 
70
        Release: <span class="version-num">0.7.7</span> | Release Date: May 5, 2012
71
71
 
72
72
 
73
73
    </div>
114
114
    <h3><a href="../index.html">Table of Contents</a></h3>
115
115
    <ul>
116
116
<li><a class="reference internal" href="#">Drizzle</a><ul>
117
 
<li><a class="reference internal" href="#supported-versions-and-features">Supported Versions and Features</a></li>
118
117
<li><a class="reference internal" href="#connecting">Connecting</a></li>
119
 
<li><a class="reference internal" href="#connection-timeouts">Connection Timeouts</a></li>
120
 
<li><a class="reference internal" href="#storage-engines">Storage Engines</a></li>
121
 
<li><a class="reference internal" href="#keys">Keys</a></li>
122
 
<li><a class="reference internal" href="#drizzle-sql-extensions">Drizzle SQL Extensions</a></li>
123
118
<li><a class="reference internal" href="#drizzle-data-types">Drizzle Data Types</a></li>
124
119
<li><a class="reference internal" href="#module-sqlalchemy.dialects.drizzle.mysqldb">MySQL-Python Notes</a><ul>
125
120
<li><a class="reference internal" href="#id1">Connecting</a></li>
126
 
<li><a class="reference internal" href="#unicode">Unicode</a></li>
127
 
<li><a class="reference internal" href="#known-issues">Known Issues</a></li>
128
121
</ul>
129
122
</li>
130
123
</ul>
158
151
<div class="section" id="module-sqlalchemy.dialects.drizzle.base">
159
152
<span id="drizzle"></span><h1>Drizzle<a class="headerlink" href="#module-sqlalchemy.dialects.drizzle.base" title="Permalink to this headline">¶</a></h1>
160
153
<p>Support for the Drizzle database.</p>
161
 
<div class="section" id="supported-versions-and-features">
162
 
<h2>Supported Versions and Features<a class="headerlink" href="#supported-versions-and-features" title="Permalink to this headline">¶</a></h2>
163
 
<p>SQLAlchemy supports the Drizzle database starting with 2010.08.
164
 
with capabilities increasing with more modern servers.</p>
165
 
<p>Most available DBAPI drivers are supported; see below.</p>
166
 
<table border="1" class="docutils">
167
 
<colgroup>
168
 
<col width="71%" />
169
 
<col width="29%" />
170
 
</colgroup>
171
 
<thead valign="bottom">
172
 
<tr class="row-odd"><th class="head">Feature</th>
173
 
<th class="head">Minimum Version</th>
174
 
</tr>
175
 
</thead>
176
 
<tbody valign="top">
177
 
<tr class="row-even"><td>sqlalchemy.orm</td>
178
 
<td>2010.08</td>
179
 
</tr>
180
 
<tr class="row-odd"><td>Table Reflection</td>
181
 
<td>2010.08</td>
182
 
</tr>
183
 
<tr class="row-even"><td>DDL Generation</td>
184
 
<td>2010.08</td>
185
 
</tr>
186
 
<tr class="row-odd"><td>utf8/Full Unicode Connections</td>
187
 
<td>2010.08</td>
188
 
</tr>
189
 
<tr class="row-even"><td>Transactions</td>
190
 
<td>2010.08</td>
191
 
</tr>
192
 
<tr class="row-odd"><td>Two-Phase Transactions</td>
193
 
<td>2010.08</td>
194
 
</tr>
195
 
<tr class="row-even"><td>Nested Transactions</td>
196
 
<td>2010.08</td>
197
 
</tr>
198
 
</tbody>
199
 
</table>
200
 
<p>See the official Drizzle documentation for detailed information about features
201
 
supported in any given server release.</p>
202
 
</div>
 
154
<p>Drizzle is a variant of MySQL. Unlike MySQL, Drizzle&#8217;s default storage engine
 
155
is InnoDB (transactions, foreign-keys) rather than MyISAM. For more
 
156
<a class="reference external" href="http://docs.drizzle.org/mysql_differences.html">Notable Differences</a>, visit
 
157
the <a class="reference external" href="http://docs.drizzle.org/index.html">Drizzle Documentation</a>.</p>
 
158
<p>The SQLAlchemy Drizzle dialect leans heavily on the MySQL dialect, so much of
 
159
the <a class="reference internal" href="mysql.html"><em>SQLAlchemy MySQL</em></a> documentation is also relevant.</p>
203
160
<div class="section" id="connecting">
204
161
<h2>Connecting<a class="headerlink" href="#connecting" title="Permalink to this headline">¶</a></h2>
205
 
<p>See the API documentation on individual drivers for details on connecting.</p>
206
 
</div>
207
 
<div class="section" id="connection-timeouts">
208
 
<h2>Connection Timeouts<a class="headerlink" href="#connection-timeouts" title="Permalink to this headline">¶</a></h2>
209
 
<p>Drizzle features an automatic connection close behavior, for connections that
210
 
have been idle for eight hours or more.   To circumvent having this issue, use
211
 
the <tt class="docutils literal"><span class="pre">pool_recycle</span></tt> option which controls the maximum age of any connection:</p>
212
 
<div class="highlight-python"><div class="highlight"><pre><span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">&#39;drizzle+mysqldb://...&#39;</span><span class="p">,</span> <span class="n">pool_recycle</span><span class="o">=</span><span class="mi">3600</span><span class="p">)</span></pre></div>
213
 
</div>
214
 
</div>
215
 
<div class="section" id="storage-engines">
216
 
<h2>Storage Engines<a class="headerlink" href="#storage-engines" title="Permalink to this headline">¶</a></h2>
217
 
<p>Drizzle defaults to the <tt class="docutils literal"><span class="pre">InnoDB</span></tt> storage engine, which is transactional.</p>
218
 
<p>Storage engines can be elected when creating tables in SQLAlchemy by supplying
219
 
a <tt class="docutils literal"><span class="pre">drizzle_engine='whatever'</span></tt> to the <tt class="docutils literal"><span class="pre">Table</span></tt> constructor.  Any Drizzle table
220
 
creation option can be specified in this syntax:</p>
221
 
<div class="highlight-python"><div class="highlight"><pre><span class="n">Table</span><span class="p">(</span><span class="s">&#39;mytable&#39;</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
222
 
      <span class="n">Column</span><span class="p">(</span><span class="s">&#39;data&#39;</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">32</span><span class="p">)),</span>
223
 
      <span class="n">drizzle_engine</span><span class="o">=</span><span class="s">&#39;InnoDB&#39;</span><span class="p">,</span>
224
 
     <span class="p">)</span></pre></div>
225
 
</div>
226
 
</div>
227
 
<div class="section" id="keys">
228
 
<h2>Keys<a class="headerlink" href="#keys" title="Permalink to this headline">¶</a></h2>
229
 
<p>Not all Drizzle storage engines support foreign keys.  For <tt class="docutils literal"><span class="pre">BlitzDB</span></tt> and
230
 
similar engines, the information loaded by table reflection will not include
231
 
foreign keys.  For these tables, you may supply a
232
 
<tt class="xref py py-class docutils literal"><span class="pre">ForeignKeyConstraint</span></tt> at reflection time:</p>
233
 
<div class="highlight-python"><div class="highlight"><pre><span class="n">Table</span><span class="p">(</span><span class="s">&#39;mytable&#39;</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
234
 
      <span class="n">ForeignKeyConstraint</span><span class="p">([</span><span class="s">&#39;other_id&#39;</span><span class="p">],</span> <span class="p">[</span><span class="s">&#39;othertable.other_id&#39;</span><span class="p">]),</span>
235
 
      <span class="n">autoload</span><span class="o">=</span><span class="bp">True</span>
236
 
     <span class="p">)</span></pre></div>
237
 
</div>
238
 
<p>When creating tables, SQLAlchemy will automatically set <tt class="docutils literal"><span class="pre">AUTO_INCREMENT</span></tt> on
239
 
an integer primary key column:</p>
240
 
<div class="highlight-python"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">t</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">&#39;mytable&#39;</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
241
 
<span class="gp">... </span>  <span class="n">Column</span><span class="p">(</span><span class="s">&#39;mytable_id&#39;</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
242
 
<span class="gp">... </span><span class="p">)</span>
243
 
<span class="gp">&gt;&gt;&gt; </span><span class="n">t</span><span class="o">.</span><span class="n">create</span><span class="p">()</span>
244
 
<span class="go">CREATE TABLE mytable (</span>
245
 
<span class="go">        id INTEGER NOT NULL AUTO_INCREMENT,</span>
246
 
<span class="go">        PRIMARY KEY (id)</span>
247
 
<span class="go">)</span></pre></div>
248
 
</div>
249
 
<p>You can disable this behavior by supplying <tt class="docutils literal"><span class="pre">autoincrement=False</span></tt> to the
250
 
<tt class="xref py py-class docutils literal"><span class="pre">Column</span></tt>.  This flag can also be used to enable
251
 
auto-increment on a secondary column in a multi-column key for some storage
252
 
engines:</p>
253
 
<div class="highlight-python"><div class="highlight"><pre><span class="n">Table</span><span class="p">(</span><span class="s">&#39;mytable&#39;</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
254
 
      <span class="n">Column</span><span class="p">(</span><span class="s">&#39;gid&#39;</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">,</span> <span class="n">autoincrement</span><span class="o">=</span><span class="bp">False</span><span class="p">),</span>
255
 
      <span class="n">Column</span><span class="p">(</span><span class="s">&#39;id&#39;</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
256
 
     <span class="p">)</span></pre></div>
257
 
</div>
258
 
</div>
259
 
<div class="section" id="drizzle-sql-extensions">
260
 
<h2>Drizzle SQL Extensions<a class="headerlink" href="#drizzle-sql-extensions" title="Permalink to this headline">¶</a></h2>
261
 
<p>Many of the Drizzle SQL extensions are handled through SQLAlchemy&#8217;s generic
262
 
function and operator support:</p>
263
 
<div class="highlight-python"><div class="highlight"><pre><span class="n">table</span><span class="o">.</span><span class="n">select</span><span class="p">(</span><span class="n">table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">password</span><span class="o">==</span><span class="n">func</span><span class="o">.</span><span class="n">md5</span><span class="p">(</span><span class="s">&#39;plaintext&#39;</span><span class="p">))</span>
264
 
<span class="n">table</span><span class="o">.</span><span class="n">select</span><span class="p">(</span><span class="n">table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">username</span><span class="o">.</span><span class="n">op</span><span class="p">(</span><span class="s">&#39;regexp&#39;</span><span class="p">)(</span><span class="s">&#39;^[a-d]&#39;</span><span class="p">))</span></pre></div>
265
 
</div>
266
 
<p>And of course any valid Drizzle statement can be executed as a string as well.</p>
267
 
<p>Some limited direct support for Drizzle extensions to SQL is currently
268
 
available.</p>
269
 
<ul>
270
 
<li><p class="first">SELECT pragma:</p>
271
 
<div class="highlight-python"><div class="highlight"><pre><span class="n">select</span><span class="p">(</span><span class="o">...</span><span class="p">,</span> <span class="n">prefixes</span><span class="o">=</span><span class="p">[</span><span class="s">&#39;HIGH_PRIORITY&#39;</span><span class="p">,</span> <span class="s">&#39;SQL_SMALL_RESULT&#39;</span><span class="p">])</span></pre></div>
272
 
</div>
273
 
</li>
274
 
<li><p class="first">UPDATE with LIMIT:</p>
275
 
<div class="highlight-python"><div class="highlight"><pre><span class="n">update</span><span class="p">(</span><span class="o">...</span><span class="p">,</span> <span class="n">drizzle_limit</span><span class="o">=</span><span class="mi">10</span><span class="p">)</span></pre></div>
276
 
</div>
277
 
</li>
278
 
</ul>
 
162
<p>See the individual driver sections below for details on connecting.</p>
279
163
</div>
280
164
<div class="section" id="drizzle-data-types">
281
165
<h2>Drizzle Data Types<a class="headerlink" href="#drizzle-data-types" title="Permalink to this headline">¶</a></h2>
404
288
below).</li>
405
289
<li><strong>strict</strong> &#8211; Defaults to False: ensure that a given value is in this
406
290
ENUM&#8217;s range of permissible values when inserting or updating rows.
407
 
Note that Drizzle will not raise a fatal error if you attempt to store
408
 
an out of range value- an alternate value will be stored instead.
 
291
Note that Drizzle will not raise a fatal error if you attempt to
 
292
store an out of range value- an alternate value will be stored
 
293
instead.
409
294
(See Drizzle ENUM documentation.)</li>
410
295
<li><strong>collation</strong> &#8211; Optional, a column-level collation for this string
411
296
value.  Takes precedence to &#8216;binary&#8217; short-hand.</li>
612
497
</div>
613
498
<div class="section" id="module-sqlalchemy.dialects.drizzle.mysqldb">
614
499
<span id="mysql-python-notes"></span><h2>MySQL-Python Notes<a class="headerlink" href="#module-sqlalchemy.dialects.drizzle.mysqldb" title="Permalink to this headline">¶</a></h2>
615
 
<p>Support for the Drizzle database via the Drizzle-python adapter.</p>
616
 
<p>Drizzle-Python is available at:</p>
 
500
<p>Support for the Drizzle database via the mysql-python adapter.</p>
 
501
<p>MySQL-Python is available at:</p>
617
502
<blockquote>
618
503
<div><a class="reference external" href="http://sourceforge.net/projects/mysql-python">http://sourceforge.net/projects/mysql-python</a></div></blockquote>
619
 
<p>At least version 1.2.1 or 1.2.2 should be used.</p>
620
504
<div class="section" id="id1">
621
505
<h3>Connecting<a class="headerlink" href="#id1" title="Permalink to this headline">¶</a></h3>
622
506
<p>Connect string format:</p>
623
507
<div class="highlight-python"><pre>drizzle+mysqldb://&lt;user&gt;:&lt;password&gt;@&lt;host&gt;[:&lt;port&gt;]/&lt;dbname&gt;</pre>
624
508
</div>
625
509
</div>
626
 
<div class="section" id="unicode">
627
 
<h3>Unicode<a class="headerlink" href="#unicode" title="Permalink to this headline">¶</a></h3>
628
 
<p>Drizzle accommodates Python <tt class="docutils literal"><span class="pre">unicode</span></tt> objects directly and 
629
 
uses the <tt class="docutils literal"><span class="pre">utf8</span></tt> encoding in all cases.</p>
630
 
</div>
631
 
<div class="section" id="known-issues">
632
 
<h3>Known Issues<a class="headerlink" href="#known-issues" title="Permalink to this headline">¶</a></h3>
633
 
<p>Drizzle-python at least as of version 1.2.2 has a serious memory leak related
634
 
to unicode conversion, a feature which is disabled via <tt class="docutils literal"><span class="pre">use_unicode=0</span></tt>.
635
 
The recommended connection form with SQLAlchemy is:</p>
636
 
<div class="highlight-python"><div class="highlight"><pre><span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">&#39;mysql://scott:tiger@localhost/test?charset=utf8&amp;use_unicode=0&#39;</span><span class="p">,</span> <span class="n">pool_recycle</span><span class="o">=</span><span class="mi">3600</span><span class="p">)</span></pre></div>
637
 
</div>
638
 
</div>
639
510
</div>
640
511
</div>
641
512