Contact: fumanchu@aminus.org

Log in as guest/dejavu to create tickets

I think I've seen this ORM somewhere before...

root/branches/crazycache/dejavu/doc/storage.html

Revision 543 (checked in by fumanchu, 5 years ago)

Changed to unit(cls, **kwargs) sig throughout. Also changed sandbox.recall to include order, limit, and offset args; removed **kwargs from recall, but the 'expr' arg may now be a dict for all x/multi/recall methods throughout. Removed inheritance code. Added a 'sum' method to StorageManager?.

Line 
1 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
2    "http://www.w3.org/TR/xhtml1/DTD/strict.dtd">
3 <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
4
5 <head>
6     <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
7     <title>Dejavu: Configuring Storage</title>
8     <link href='dejavu.css' rel='stylesheet' type='text/css' />
9
10 <style type='text/css'>
11
12 td.notsup {
13     background-color: #FFCCCC;
14 }
15
16 td.python {
17     background-color: #FFFFCC;
18 }
19
20 </style>
21 </head>
22
23 <body>
24
25 <h2>Deployers: Configuring Storage</h2>
26
27 <p>The topmost object in Dejavu is a <tt>StorageManager</tt> object.
28 When building a Dejavu application, you must first create a
29 StorageManager instance, and must find a way to persist this object
30 across client connections. This can be achieved in multiple ways;
31 web applications, for example, will typically create a single process
32 to serve all requests. Desktop applications will probably create a
33 single StorageManager object for each running instance of the program.</p>
34
35 <p>Storage Managers insulate an application developer from the specifics of
36 databases, query languages, and cache mechanisms. As the <i>deployer</i> of
37 a Dejavu application, you get to be in control of these specifics. But
38 don't worry; in the vast majority of cases, you will set up a single
39 database with just two lines in a configuration file. Often, the
40 application developer will have already prepared default config files
41 which you can simply "plug and play". But if you <i>need</i> more control
42 over your data storage, you have it.</p>
43
44 <p>When you deploy an app built with Dejavu, you must specify Storage
45 Managers to use for persisting application objects. This is usually
46 done through an ini-style configuration file, although Dejavu itself
47 doesn't currently provide a parser for that. Here's a short example
48 of configuring a store in Python:
49 <pre>
50 from dejavu import storage
51 opts = {'connections.Connect': "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=D:\data\junct.mdb;"}
52 root = storage.resolve("access", opts)
53 </pre>
54 The <tt class='def'>storage.resolve(store, options=None)</tt> call
55 tells Dejavu the <i>class</i> of SM we'd like to use.
56 For most applications, you'll decide which class to use based on the
57 database you want to use. Our example declares that we want to persist our
58 application data in an "MS Access" (i.e., Jet) database. You may supply a
59 known short name (like "sqlite") or the full dotted-package name.
60 <tt class='def'>storage.managers</tt> is a dict of short names to
61 full classes (or dotted class names). If you're including Dejavu in
62 a larger framework, feel free to add to this registry.</p>
63
64 <p>The options dict we pass in our example includes a standard ADO Connect
65 string. The MS Access class requires this entry; other SM's may not.</p>
66
67
68 <a name='databases'><h3>Database Storage Managers</h3></a>
69
70 <h4>Microsoft SQL Server / Microsoft Access (Jet)</h4>
71 <p>This module was developed against ADO 2.7 and 2.8,
72     using MSDE, SQL Server 2000/2005, and Access 2000.</p>
73
74 <p>Classes:</p>
75 <ul>
76     <li>"sqlserver" (<tt>dejavu.storage.storeado.StorageManagerADO_SQLServer</tt>)</li>
77     <li>"[ms]access" (<tt>dejavu.storage.storeado.StorageManagerADO_MSAccess</tt>)</li>
78 </ul>
79
80 <p>Options:</p>
81 <ul>
82     <li><b>connections.Connect:</b> A valid ADO connect string. There are
83         plenty of online references for how to form these; for example, at
84         <a href='http://support.microsoft.com/?kbid=193332'>Microsoft</a>.</li>
85 </ul>
86
87 <h4>PostgreSQL</h4>
88 <p>This class was developed against
89     PostgreSQL 8.0.0 rc-1 (Win2k),
90     PostgreSQL 8.2.4 on i686-pc-mingw32 (Vista),
91     and also tested on
92     PostgreSQL 7.6.6-6 on Debian "sarge",
93     using
94     pyPgSQL-2.5.1 and psycopg2-2.0.6/2.0.5.1
95 </p>
96
97 <p>Classes:</p>
98 <ul>
99     <li>"postgres[ql]" or "pypgsql" (<tt>dejavu.storage.storepypgsql.StorageManagerPgSQL</tt>)</li>
100     <li>"psycopg[2]" (<tt>dejavu.storage.storepsycopg.StorageManagerPsycoPg</tt>)</li>
101 </ul>
102
103 <p>Options:</p>
104 <ul>
105     <li><b>connections.Connect:</b> A connect string of the form "k=v k=v". For example,
106         <tt>"host=localhost dbname=myapp user=postgres password=hilar1ous"</tt>.
107         See the <a href='http://www.postgresql.org/docs/current/static/libpq.html'>libpq</a>
108         docs for complete information.</li>
109 </ul>
110
111 <h4>MySQL (MySQLdb)</h4>
112 <p>This class was developed against
113     mysql  Ver 14.7 Distrib 4.1.8, for Win95/Win98 (i32),
114     and also tested on
115     mysql  Ver 12.22 Distrib 4.0.23, for pc-linux-gnu (i386), and
116     5.0.45.community.nt (Vista)
117 </p>
118
119 <p>Classes:</p>
120 <ul>
121     <li>"mysql" (<tt>dejavu.storage.storemysql.StorageManagerMySQL</tt>)</li>
122 </ul>
123
124 <p>Options:</p>
125 <ul>
126     <li>Connection arguments: any of "host", "user", "passwd", "db", "port",
127         "unix_socket", "client_flag".<br />See the
128         <a href='http://dev.mysql.com/doc/mysql/en/mysql_real_connect.html'>docs</a>
129         for complete info.</li>
130 </ul>
131
132 <h4>SQLite (pysqlite/sqlite3)</h4>
133 <p>This class was developed against
134     sqlite 3.0.8 (pysqlite-1.1.6.win32-py2.3),
135     sqlite 3.3.3 (pysqlite-1.1.7.win32-py2.4),
136     sqlite 2.8.15-3 on Debian "sarge",
137     sqlite 3.3.4 (python 2.5 on win2k),
138     and sqlite 3.4.0 (pysqlite-2.3.5.win32-py2.4) on Vista.
139     If you have Python 2.5 or later, the builtin _sqlite3 library
140     will be used; otherwise, you need to install pysqlite.
141 </p>
142
143 <p>Classes:</p>
144 <ul>
145     <li>"sqlite" (<tt>dejavu.storage.storesqlite.StorageManagerSQLite</tt>)</li>
146 </ul>
147
148 <p>Options:</p>
149 <ul>
150     <li><b>Database:</b> Filename of the database. May be a relative path.</li>
151     <li><b>Mode:</b> Optional. DB file mode. Defaults to 0755.</li>
152 </ul>
153
154
155 <h4>Firebird (kinterbasdb)</h4>
156 <p>This class was developed against:
157     <ul>
158         <li>KInterbasDB Version: (3, 2, 0, 'alpha', 1) and
159             Server Version: 'WI-V1.5.2.4731 Firebird 1.5' on Win2k,</li>
160         <li>KInterbasDB Version: (3, 2, 0, 'final', 0) and
161             Server Version: 'WI-V2.0.3.12981 Firebird 2.0' on Vista.</li>
162     </ul>
163 </p>
164
165 <p>Classes:</p>
166 <ul>
167     <li>"firebird" (<tt>dejavu.storage.storefirebird.StorageManagerFirebird</tt>)</li>
168 </ul>
169
170 <p>Options:</p>
171 <ul>
172     <li><b>Name:</b> Filename of the database. Must be an absolute path.</li>
173     <li><b>Host:</b> The TCP host name, usually "localhost".</li>
174     <li><b>User:</b> The user name (e.g. "sysdba").</li>
175     <li><b>Password:</b> The password for the given user name.</li>
176     <li><b>Encoding:</b> The charset to be used in each connect() call.</li>
177 </ul>
178
179 <p><b>The Firebird Storage Manager is new and not yet fully thread-safe.
180 Patches welcome.</b></p>
181
182 <h4>Common Database Configuration Entries</h4>
183 <p>In addition to the above, Storage Managers for databases (probably)
184 accept these additional options:</p>
185
186 <table>
187 <tr><th>Key</th><th>Example Value</th><th>Description</th></tr>
188 <tr>
189     <td>schemaclass.prefix</td>
190     <td><tt>myapp_</tt></td>
191     <td>Optional. If specified, all tables in the database will have names
192     starting with this prefix. If not provided, it defaults to "" (empty).
193     This helps if you need to mix Dejavu tables with tables from another
194     application. Leave blank if you want no prefix.</td>
195 </tr>
196 <tr>
197     <td>connections.poolsize</td>
198     <td><tt>10</tt></td>
199     <td>Optional. Defaults to 10. If nonzero, connections will be pooled
200         (up to a total equal to <i>Pool Size</i>). If zero, no pool
201         will be used; each statement (!) will use a new connection.</td>
202 </tr>
203 <tr>
204     <td>connections.implicit_trans</td>
205     <td><tt>False</tt></td>
206     <td>Optional. Defaults to False. If True, a new connection will
207         automatically call "START TRANSACTION". It will also be associated
208         with the current thread, and any subsequent calls on the same thread
209         will then return the same connection object.</td>
210 </tr>
211 <tr>
212     <td>connections.contention</td>
213     <td><tt>'commit'</tt></td>
214     <td>Optional. If 'commit' (the default), schema-modifying commands
215         (e.g. add_property) will autocommit any pending transactions.
216         Change this to 'error' if you'd rather play it safe.</td>
217 </tr>
218 <tr>
219     <td>connections.default_isolation</td>
220     <td><tt>"READ COMMITTED"</tt></td>
221     <td>Optional. All database SM's already have a value for this, but you
222         can select another if you wish. This value should be a "native value"
223         for your database's particular transaction mechanisms. For example,
224         PostgreSQL uses ANSI/SQL names like "READ COMMITTED", but Firebird
225         uses library constants like <tt>kinterbasdb.isc_tpb_read_committed</tt>.</td>
226 </tr>
227 </table>
228
229
230 <a name='other'><h3>Other Storage Managers</h3></a>
231
232 <h4>RAM</h4>
233 <p>Persists Units in RAM; all Units are lost when the process exits.</p>
234
235
236 <h4>Memcached</h4>
237
238 <p><b>External Dependency:
239     <a href='http://www.tummy.com/Community/software/python-memcached/'
240         >python-memcached</a></b></p>
241
242 <p>Persists Units to a set of
243 <a href='http://www.danga.com/memcached/'>memcached</a> servers.
244 This is an extremely simple implementation; every value that is not
245 of type <tt>str</tt> or <tt>int</tt> is pickled. Querying will be slow--
246 every Unit is sucked in one-by-one and tested in pure Python.
247 But for many cache applications, you don't need heavyweight query tools.</p>
248
249 <p>Classes:</p>
250 <ul><li>"memcache[d]" (<tt>dejavu.storage.storememcached.MemcachedStorageManager</tt>)</li></ul>
251
252 <p>Options:</p>
253 <ul>
254     <li><b>name:</b> Required. This string will be used to form namespaced
255         memcached keys.</li>
256     <li><b>memcached.servers:</b> Required. A list of strings of the form
257         'IP-address:port'. These will be passed directly into the
258         memcache.Client instance.</li>
259     <li><b>memcached.indexed:</b> if True (the default), this store will
260         maintain an index of all stored objects in memcached itself. This
261         is the 'safe' choice, and necessary if your only store is memcached.
262         If you run this store as an ObjectCache.cache, however, you should
263         turn this off, allowing ObjectCache.nextstore to maintain the
264         indexes--this allows the cache to run orders of magnitude faster.</li>
265 </ul>
266
267
268 <h4>JSON</h4>
269
270 <p><b>External Dependency:
271     <a href='http://undefined.org/python/#simplejson'>simplejson</a></b></p>
272
273 <p>Persists Units to a filesystem, one folder per class. Each folder
274 contains files, one per Unit, with the Unit identity as the file name.
275 Each of those unit files contains a JSON dict of Unit property values.
276 For example:</p>
277
278 <pre>
279 root/
280     Album/
281     |   78952.json
282     Song/
283         1372.json
284         88.json
285 </pre>
286
287 <p>Querying will be slow--every Unit is sucked in one-by-one and tested in
288 pure Python. This is a good choice for test data or system tables--store
289 the data in JSON format for pretty version-control diffs, then migrate it
290 to another store when you run the tests or start the application.</p>
291
292 <p>Classes:</p>
293 <ul><li>"json" (<tt>dejavu.storage.storejson.StorageManagerJSON</tt>)</li></ul>
294
295 <p>Options:</p>
296 <ul>
297     <li><b>root:</b> Required. The file path (directory) in which to
298         place db files. Each Unit class will get its own subfolder,
299         of the same name as the class.</li>
300     <li><b>mode:</b> Optional. The mode arg to pass to <tt>os.mkdir</tt>
301         when creating folders. Defaults to '0777'.</li>
302     <li><b>idsepchar:</b> Optional. The character to use for separating
303         unit identities which are multivalent. Defaults to '_' (underscore).
304         For example, a Unit with <tt>identifiers = ('Name', 'DOB')</tt>
305         would get a folder name like 'Fred_20040321'.</li>
306     <li><b>encoding:</b> Passed to the simplejson.Decoder.</li>
307     <li><b>skipkeys:</b> Passed to the simplejson.Encoder.
308         Defaults to False.</li>
309     <li><b>check_circular:</b> Passed to the simplejson.Encoder.
310         Defaults to True.</li>
311     <li><b>allow_nan:</b> Passed to the simplejson.Encoder.
312         Defaults to False.</li>
313     <li><b>indent:</b> Passed to the simplejson.Encoder.
314         Defaults to None.</li>
315 </ul>
316
317
318 <h4>Shelve</h4>
319 <p>Persists Units to shelve-type files. Extremely simple implementation;
320 everything is pickled. Querying will be slow--every Unit is sucked in
321 one-by-one and tested in pure Python using <tt>Expression(unit)</tt>.
322 But for many applications, you don't need heavyweight query tools;
323 for example, an online forum may only need topic content looked up by ID.
324 Or small system tables that only get read at startup might benefit.</p>
325
326 <p class='warning'><b>Developers note:</b> The shelve implementation in
327 Dejavu does not use "writeback"; that is, changes you make to data are
328 stored only in memory until each shelf has its <tt>close</tt> method
329 called. If <tt>close</tt> is never called, your changes are lost!
330 The easiest way to ensure that your changes are saved is to call
331 store.shutdown() when your app is closing. Since one of the design
332 goals of Dejavu is to allow deployers to choose which backend to use,
333 your applications should <i>always</i> guarantee that store.shutdown()
334 is called on program exit.</p>
335
336 <p>Classes:</p>
337 <ul><li>"shelve" (<tt>dejavu.storage.storeshelve.StorageManagerShelve</tt>)</li></ul>
338
339 <p>Options:</p>
340 <ul>
341     <li><b>Path:</b> The file path (directory) in which to place db files.
342         Each Unit subclass will get its own file, of the same name as the
343         subclass.</li>
344 </ul>
345
346
347 <h4>Folders</h4>
348 <p>Persists Units to a filesystem, one folder per class. Each folder
349 contains subfolders, one per Unit, with the Unit identity as the folder
350 name. Each of those unit folders contains one file for each Unit
351 Property. For example:</p>
352
353 <pre>
354 root/
355     Album/
356     |   78952/
357     |       Name.txt
358     |       Artist.txt
359     Song/
360         1372/
361         |   AlbumID.txt
362         |   Data.mp3
363         88/
364             AlbumID.txt
365             Data.mp3
366 </pre>
367
368 <p>This is an extremely simple implementation; every value that is not
369 of type <tt>str</tt> is pickled. Querying will be slow--every Unit is
370 sucked in one-by-one and tested in pure Python.
371 But for many applications, you don't need heavyweight query tools;
372 for example, an upload site may only need files looked up by ID.</p>
373
374 <p>Classes:</p>
375 <ul><li>"folders" (<tt>dejavu.storage.storeshelve.StorageManagerShelve</tt>)</li></ul>
376
377 <p>Options:</p>
378 <ul>
379     <li><b>root:</b> Required. The file path (directory) in which to
380         place db files. Each Unit class will get its own subfolder,
381         of the same name as the class.</li>
382     <li><b>mode:</b> Optional. The mode arg to pass to <tt>os.mkdir</tt>
383         when creating folders. Defaults to '0777'.</li>
384     <li><b>idsepchar:</b> Optional. The character to use for separating
385         unit identities which are multivalent. Defaults to '_' (underscore).
386         For example, a Unit with <tt>identifiers = ('Name', 'DOB')</tt>
387         would get a folder name like 'Fred_20040321'.</li>
388     <li><b>extdefault:</b> Optional. The default file extension to use
389         for Unit Property files. Defaults to '.txt'.</li>
390     <li><b>&lt;unit&gt;.&lt;propname&gt;:</b> Optional. The value should
391         be the file extension for properties of the given propname
392         for the given unit class. For example, <tt>Song.Data = .mp3</tt>
393         (be sure to include the leading 'dot' if you want one).</li>
394 </ul>
395
396
397 <a name='middleware'><h3>Middleware</h3></a>
398
399 <p>Some Storage Managers act as "middleware", and can be chained together
400 to provide layered functionality. Consider, for example, the
401 <tt>ObjectCache</tt> class; it has another Storage Manager
402 "behind it", which it proxies. It can be used to cache objects between
403 client connections independently from the underlying, database-specific
404 Storage Manager. The beauty of this design is that the decision to
405 use a ObjectCache is completely up to the deployer, <i>not</i> the
406 application developer. The deployer can separate stores, test response
407 times, and address other integration concerns on their own systems.</p>
408
409 <h4>Object Cache</h4>
410 <p>Use this class to persist Units in memory between client connections.
411 It must proxy another Storage Manager.</p>
412
413 <p>Classes:</p>
414 <ul><li>"cache" (<tt>dejavu.storage.caching.ObjectCache</tt>)</li></ul>
415
416 <p>Options:</p>
417 <ul>
418     <li><b>Next Store:</b> Required. The next Storage Manager in the chain.</li>
419     <li><b>cache:</b> Optional. The Storage Manager to use for the cache.
420         If not given, it defaults to a RAM store.</li>
421 </ul>
422
423 <h4>Aged Cache</h4>
424 <p>Use this class to persist Units in memory between client connections.
425 It must proxy another Storage Manager.</p>
426
427 <p>Classes:</p>
428 <ul><li>"aged" (<tt>dejavu.storage.caching.AgedCache</tt>)</li></ul>
429
430 <p>Options:</p>
431 <ul>
432     <li><b>Next Store:</b> Required. The next Storage Manager in the chain.</li>
433     <li><b>cache:</b> Optional. The Storage Manager to use for the cache.
434         If not given, it defaults to a RAM store.</li>
435     <li><b>Lifetime:</b> Optional. The recurrence string which declares
436         how often to sweep Units out of the in-memory cache. The string you
437         supply should be one of the following types:
438         <ul>
439             <li><b>By units (intervals):</b> "3 hours" will run every 3
440                 hours. "7 days" or "1 week" will run once each week.</li>
441             <li><b>Daily:</b> "14:00 each day" will run at 2:00 P.M.
442                 every day.</li>
443             <li><b>Weekly:</b> "Mon", "Monday", or "Mondays" will run once
444                 each Monday.</li>
445             <li><b>Monthly:</b> "20 each month" will run on the 20th of
446                 each month. "0 every month" will run on the <i>last</i>
447                 day of each month.</li>
448         </ul>
449         See the <tt>recur</tt> module for complete options.
450     </li>
451 </ul>
452
453
454 <h4>Burned Cache</h4>
455 <p>Use this class to persist Units in memory between client connections.
456 It needs another Storage Manager to proxy. Unlike the ObjectCache above,
457 this Storage Manager recalls all Units at once upon the first request,
458 and won't recall them again from storage. They are "burned" into memory
459 for the lifetime of the application.</p>
460
461 <p>Classes:</p>
462 <ul><li>"burned" (<tt>dejavu.storage.caching.BurnedCache</tt>)</li></ul>
463
464 <p>Options:</p>
465 <ul>
466     <li><b>Next Store:</b> Required. The next Storage Manager in the chain.</li>
467     <li><b>cache:</b> Optional. The Storage Manager to use for the cache.
468         If not given, it defaults to a RAM store.</li>
469 </ul>
470
471 <a name='partitioning'><h3>Partitioning</h3></a>
472
473 <h4>Vertical Partitioner</h4>
474 <p>This class replaces the old Arena object from Dejavu 1.x. It allows you
475 to aggregate multiple stores into a single interface, partitioned by Unit
476 class. Unlike most other StorageManagers, it takes no options. Instead,
477 you will generally set this as the root of your storage graph and
478 repeatedly call its <tt class='def'>add_store(name, store)</tt> method.
479 There's also a corresponding <tt class='def'>remove_store(name)</tt>
480 method.</p>
481
482 <p>Once you've added stores, the <tt class='def'>stores</tt> attribute is a
483 dict from store names to StorageManager instances. However, you shouldn't
484 manipulate this directly--use add/remove_store instead. When you call
485 add_store, it will also set up the partitioner's <tt class='def'>classmap</tt>
486 attribute, which is used to direct queries and other command to the correct
487 store(s) based on the class. DDL methods will generally dispatch to all
488 stores for each class. DML methods will generally dispatch to
489 <tt>classmap[unit.__class__][0]</tt>; those which involve multiple
490 classes (e.g. multirecall), will try to find a single store which
491 handles all classes in the given relation. To override this default
492 search, you can add entries to <tt>classmap</tt> of the form:
493 <tt>{(clsA, clsB, clsC): [store1]}</tt>, which instructs the
494 partitioner to use the given store for any Join with the same
495 order, such as <tt>(clsA &lt;&lt; clsB) &amp; clsC</tt>.</p>
496
497 <p>Classes:</p>
498 <ul><li><tt>dejavu.storage.partitions.VerticalPartitioner</tt></li></ul>
499
500 <p>Options:</p>
501 <ul>
502     <li>None</li>
503 </ul>
504
505 <a name='comparison'><h3>SM Comparison Chart</h3></a>
506
507 <p>When selecting a storage implementation, you should be aware of the
508 strengths and limitations of each option. The following chart should help
509 you decide.</p>
510
511 <p>First, it shows you which stores do and do not support certain
512 optional features of Dejavu. Your application developer should provide you
513 with a list of any features which they <i>require</i>.</p>
514
515 <p>Second, it shows you which stores have performance or boundary issues
516 and where. When developing applications, you should avoid these issues
517 either by coding alternative solutions, or by recommending to your
518 deployers that they avoid the problematic stores. Note that some
519 limitations are inherent in the storage mechanism itself, while some
520 are limitations of the current Storage Manager for that mechanism.</p>
521
522 <ul>
523     <li><b>Y</b>: The store supports the feature natively.</li>
524     <li><b>P</b>: The store does not provide the feature natively, but
525         Dejavu provides a fallback in pure Python (which may be slower).
526         Boundaries and limitations are therefore Python limits.</li>
527     <li><b>N</b>: The store does not allow the feature at all.</li>
528     <li>&lt;blank&gt;: Unknown/not yet documented.</li>
529 </ul>
530
531 <table>
532 <tr>
533     <th></th>
534     <th>access</th>
535     <th>firebird</th>
536     <th>mysql</th>
537     <th>postgres</th>
538     <th>sqlite</th>
539     <th>sqlserver</th>
540     <th>ram</th>
541     <th>memcached</th>
542     <th>shelve</th>
543     <th>folders</th>
544     <th>json</th>
545 </tr>
546
547 <tr>
548     <td>Connection Pool <a href='#connpool'>[5]</a></td>
549     <td class='notsup'>N (single only)</td>
550     <td class='python'>P</td>
551     <td class='python'>P</td>
552     <td class='python'>P</td>
553     <td class='python'>P</td>
554     <td class='python'>P</td>
555     <td class='notsup'>N</td>
556     <td class='notsup'>N</td>
557     <td class='notsup'>N</td>
558     <td class='notsup'>N</td>
559     <td class='notsup'>N</td>
560 </tr>
561
562 <tr>
563     <td>Transactions</td>
564     <td>Y</td>
565     <td>Y</td>
566     <td>Y</td>
567     <td>Y</td>
568     <td>Y</td>
569     <td>Y</td>
570     <td class='notsup'>N</td>
571     <td class='notsup'>N</td>
572     <td class='notsup'>N</td>
573     <td class='notsup'>N</td>
574     <td class='notsup'>N</td>
575 </tr>
576
577 <tr>
578     <td>Indexes</td>
579     <td>Y</td>
580     <td>Y</td>
581     <td>Y</td>
582     <td>Y</td>
583     <td>Y</td>
584     <td>Y</td>
585     <td class='notsup'>N</td>
586     <td class='notsup'>N</td>
587     <td class='notsup'>N</td>
588     <td class='notsup'>N</td>
589     <td class='notsup'>N</td>
590 </tr>
591
592 <tr>
593     <td>Max identifier length</td>
594     <td>64</td>
595     <td>31</td>
596     <td>64</td>
597     <td>63</td>
598     <td>no limit?</td>
599     <td>128</td>
600     <td class='python'>P</td>
601     <td class='python'>P</td>
602     <td class='python'>P</td>
603     <td>OS-dependent</td>
604     <td>OS-dependent</td>
605 </tr>
606
607 <tr>
608     <td>Case-sensitive identifiers</td>
609     <td>Y</td>
610     <td>Y</td>
611     <td>Unix only</td>
612     <td>Y</td>
613     <td>Y</td>
614     <td>Y</td>
615     <td>Y</td>
616     <td>Y</td>
617     <td>Y</td>
618     <td>Y <a href='#filenames'>[3]</a></td>
619     <td>Y <a href='#filenames'>[3]</a></td>
620 </tr>
621
622 <tr>
623     <td>Case-sensitive LIKE ("a in b")</td>
624     <td class='python'>P</td>
625     <td>Y</td>
626     <td>Y</td>
627     <td>Y</td>
628     <td>Y</td>
629     <td class='python'>P</td>
630     <td class='python'>P</td>
631     <td class='python'>P</td>
632     <td class='python'>P</td>
633     <td class='python'>P</td>
634     <td class='python'>P</td>
635 </tr>
636
637 <tr>
638     <td>Case-sensitive string comparison ("a" &gt; "A")</td>
639     <td><tt>&lt;, &lt;=, ==, !=, &gt;, &gt;=</tt></td>
640     <td>Y</td>
641     <td>Y</td>
642     <td>Y</td>
643     <td>Y</td>
644     <td><tt>&lt;, &lt;=, ==, !=, &gt;, &gt;=</tt></td>
645     <td class='python'>P</td>
646     <td class='python'>P</td>
647     <td class='python'>P</td>
648     <td class='python'>P</td>
649     <td class='python'>P</td>
650 </tr>
651
652 <tr>
653     <td>Wildcard literals in LIKE ("a in b")</td>
654     <td>Y</td>
655     <td>Y</td>
656     <td>Y</td>
657     <td>Y</td>
658     <td>3.0.8+</td>
659     <td>Y</td>
660     <td class='python'>P</td>
661     <td class='python'>P</td>
662     <td class='python'>P</td>
663     <td class='python'>P</td>
664     <td class='python'>P</td>
665 </tr>
666
667 <tr>
668     <td>Autoincrement</td>
669     <td>Y</td>
670     <td>Y</td>
671     <td>Y</td>
672     <td>Y</td>
673     <td>3.1.0+</td>
674     <td>Y</td>
675     <td class='python'>P</td>
676     <td class='python'>P</td>
677     <td class='python'>P</td>
678     <td class='python'>P</td>
679     <td class='python'>P</td>
680 </tr>
681
682 <tr>
683     <td>add/drop/rename property</td>
684     <td>Y</td>
685     <td>Y</td>
686     <td>Y</td>
687     <td>Y</td>
688     <td class='python'>P <a href='#sqlite-alter-table'>[2]</a><br />(add: 3.2.0+)</td>
689     <td>Y</td>
690     <td>Y</td>
691     <td>Y</td>
692     <td>Y</td>
693     <td>Y</td>
694     <td>Y</td>
695 </tr>
696
697 <tr>
698     <th></th>
699     <th>access</th>
700     <th>firebird</th>
701     <th>mysql</th>
702     <th>postgres</th>
703     <th>sqlite</th>
704     <th>sqlserver</th>
705     <th>ram</th>
706     <th>memcached</th>
707     <th>shelve</th>
708     <th>folders</th>
709     <th>json</th>
710 </tr>
711
712 <tr>
713     <td>fixed point/decimal precision (in decimal digits)</td>
714     <td>12</td>
715     <td>18</td>
716     <td>16</td>
717     <td>1000</td>
718     <td>0 (always uses TEXT instead)</td>
719     <td>12</td>
720     <td class='python'>P (pickle)</td>
721     <td class='python'>P (pickle)</td>
722     <td class='python'>P (pickle)</td>
723     <td class='python'>P (pickle)</td>
724     <td>Python limit?</td>
725 </tr>
726
727 <tr>
728     <td>Max str/unicode bytes</td>
729     <td>1 GB <a href='#memofields'>[6]</a></td>
730     <td>32765 (255 for an index)</td>
731     <td>8000 (row limit)</td>
732     <td>1 GB?</td>
733     <td>1 MB (row limit)</td>
734     <td>8000 <a href='#ntext-bytes'>[4]</a></td>
735     <td class='python'>P (pickle)</td>
736     <td>1 MB (object limit, adjustable)</td>
737     <td class='python'>P (pickle)</td>
738     <td class='python'>P (pickle)</td>
739     <td>Python limit?</td>
740 </tr>
741
742 <tr>
743     <td>datetime ranges</td>
744     <td>0100-01-01 to 9999-12-31</td>
745     <td>1753-01-01 to 9999-12-31</td>
746     <td>1000-01-01 00:00:00 to 9999-12-31 23:59:59</td>
747     <td>4713 BC to 5874897 AD</td>
748     <td>4714-11-24 BC to ???</td>
749     <td>1753-01-01 00:00:00.0 to 9999-12-31 23:59:59.997</td>
750     <td class='python'>P</td>
751     <td class='python'>P</td>
752     <td class='python'>P</td>
753     <td class='python'>P</td>
754     <td class='python'>P</td>
755 </tr>
756
757 <tr>
758     <td>datetime precision</td>
759     <td>1 second</td>
760     <td>1 second</td>
761     <td>1 second</td>
762     <td>1 microsecond</td>
763     <td>1 second</td>
764     <td>1 second</td>
765     <td class='python'>P</td>
766     <td class='python'>P</td>
767     <td class='python'>P</td>
768     <td class='python'>P</td>
769     <td>1 second</td>
770 </tr>
771
772 <tr>
773     <td>dejavu.year, month, day functions</td>
774     <td>Y</td>
775     <td class='python'>P</td>
776     <td>Y</td>
777     <td>Y</td>
778     <td>3.2.3+ <a href='#perfect-dates'>[1]</a></td>
779     <td>Y</td>
780     <td class='python'>P</td>
781     <td class='python'>P</td>
782     <td class='python'>P</td>
783     <td class='python'>P</td>
784     <td class='python'>P</td>
785 </tr>
786
787 <tr>
788     <td>dejavu.now, today functions</td>
789     <td>Y</td>
790     <td>now</td>
791     <td>Y</td>
792     <td>Y</td>
793     <td>3.2.3+ <a href='#perfect-dates'>[1]</a></td>
794     <td>Y</td>
795     <td class='python'>P</td>
796     <td class='python'>P</td>
797     <td class='python'>P</td>
798     <td class='python'>P</td>
799     <td class='python'>P</td>
800 </tr>
801
802 <tr>
803     <td>startswith, endswith, containedby,
804         dejavu.icontainedby, dejavu.icontains,
805         dejavu.istartswith, dejavu.iendswith</td>
806     <td>Y</td>
807     <td>Y</td>
808     <td>Y</td>
809     <td>Y</td>
810     <td>Y</td>
811     <td>Y</td>
812     <td class='python'>P</td>
813     <td class='python'>P</td>
814     <td class='python'>P</td>
815     <td class='python'>P</td>
816     <td class='python'>P</td>
817 </tr>
818
819 <tr>
820     <td>builtin function: len</td>
821     <td>Y</td>
822     <td class='python'>P</td>
823     <td>Y</td>
824     <td>Y</td>
825     <td>Y</td>
826     <td>Y</td>
827     <td class='python'>P</td>
828     <td class='python'>P</td>
829     <td class='python'>P</td>
830     <td class='python'>P</td>
831     <td class='python'>P</td>
832 </tr>
833
834 <tr>
835     <th></th>
836     <th>access</th>
837     <th>firebird</th>
838     <th>mysql</th>
839     <th>postgres</th>
840     <th>sqlite</th>
841     <th>sqlserver</th>
842     <th>ram</th>
843     <th>memcached</th>
844     <th>shelve</th>
845     <th>folders</th>
846     <th>json</th>
847 </tr>
848
849 <tr>
850     <td>READ UNCOMMITTED</td>
851     <td>Y</td>
852     <td class='notsup'>N</td>
853     <td>Y</td>
854     <td class='notsup'>N <a href='#too-isolated'>[7]</a></td>
855     <td class='notsup'>N</td>
856     <td>Y</td>
857     <td></td>
858     <td></td>
859     <td></td>
860     <td></td>
861     <td></td>
862 </tr>
863
864 <tr>
865     <td>READ COMMITTED</td>
866     <td class='notsup'>N</td>
867     <td>Y</td>
868     <td>Y</td>
869     <td>Y</td>
870     <td class='notsup'>N</td>
871     <td>Y (timeout)</td>
872     <td></td>
873     <td></td>
874     <td></td>
875     <td></td>
876     <td></td>
877 </tr>
878 <tr>
879     <td>REPEATABLE READ</td>
880     <td class='notsup'>N</td>
881     <td class='notsup'>N <a href='#too-isolated'>[7]</a></td>
882     <td class='notsup'>N <a href='#too-isolated'>[7]</a></td>
883     <td class='notsup'>N <a href='#too-isolated'>[7]</a></td>
884     <td class='notsup'>N</td>
885     <td>Y (timeout)</td>
886     <td></td>
887     <td></td>
888     <td></td>
889     <td></td>
890     <td></td>
891 </tr>
892 <tr>
893     <td>SERIALIZABLE</td>
894     <td class='notsup'>N</td>
895     <td>Y</td>
896     <td>Y (timeout)</td>
897     <td>Y</td>
898     <td>Y <a href='#memory-trans'>[8]</a></td>
899     <td>Y (timeout)</td>
900     <td></td>
901     <td></td>
902     <td></td>
903     <td></td>
904     <td></td>
905 </tr>
906 <tr>
907     <td>Change isolation inside transaction</td>
908     <td class='notsup'>N</td>
909     <td class='notsup'>N</td>
910     <td>Y</td>
911     <td>Y</td>
912     <td class='notsup'>N</td>
913     <td>Y</td>
914     <td></td>
915     <td></td>
916     <td></td>
917     <td></td>
918     <td></td>
919 </tr>
920 </table>
921
922 <p><a name='perfect-dates'>[1]</a> In order to use native date functions in
923 SQLite, you must be storing your date and time values in one of the
924 acceptable formats. See the
925 <a href='http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions'>SQLite wiki</a>
926 for more information. Once you have verified that you are using such a format,
927 you must then set AdapterToSQLite.using_perfect_dates to True. This can be
928 done with the configuration entry: <tt>Perfect Dates: True</tt>.</p>
929
930 <p><a name='sqlite-alter-table'>[2]</a> SQLite must copy the entire table
931 to an intermediate table and then to a new, final table in order to alter
932 tables. Beginning in 3.2.0, adding columns may now be performed natively
933 (but not renaming or dropping them).</p>
934
935 <p><a name='filenames'>[3]</a> The Folders store keeps identifer values and
936 property names in folder and file names. Not all filesystems support
937 case-sensitive file/folder names.</p>
938
939 <p><a name='ntext-bytes'>[4]</a> Microsoft SQL Server does not allow
940 comparisons on string fields larger than 8000 characters.</p>
941
942 <p><a name='connpool'>[5]</a> Dejavu provides connection pool factories
943 in pure Python, and does not yet make any attempt to use native pooling
944 features.</p>
945
946 <p><a name='memofields'>[6]</a> Microsoft Access "MEMO" fields have a 1 GB
947 limit, but so does the entire database. Memo fields also cannot be used as
948 join keys; set <tt>hints['bytes'] = 255</tt> or less to use VARCHAR instead.</p>
949
950 <p><a name='memofields'>[7]</a> Some databases over-protect at various
951 isolation levels. For example, "REPEATABLE READ" should prevent fuzzy
952 reads but allow phantoms, but MySQL's and Firebird's REPEATABLE READ
953 prevent both.
954 PostgreSQL only uses two isolation levels internally, so that selecting
955 "READ UNCOMMITTED" behaves like "READ COMMITTED" and "REPEATABLE READ"
956 behaves like "SERIALIZABLE".</p>
957
958 <p><a name='memory-trans'>[8]</a> SQLite <tt>:memory:</tt> databases
959 cannot use multiple connections, so a single connection is used for
960 all threads. However, this means that transactions are generally not
961 allowed for <tt>:memory:</tt> databases when using multiple threads
962 (because multiple transactions would overlap on the same connection
963 and not be isolated at all!).</p>
964
965 </body>
966 </html>
Note: See TracBrowser for help on using the browser.