Contact: fumanchu@aminus.org

Log in as guest/geniusql to create tickets

root/trunk/geniusql/doc/storage.html

Revision 294 (checked in by lakin, 1 year ago)

removing the last vestiges of the providers that we don't have the manpower to support. Again, if someone is interested in supporting them, we will help as we can.

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>Geniusql: Configuring Storage</title>
8     <link href='geniusql.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>"Providers" insulate an application developer from the specifics of
28 databases. As the <i>deployer</i> of a Geniusql application, you get to be
29 in control of these specifics. But don't worry; in the vast majority of
30 cases, you will set up a single database with just two lines in a
31 configuration file. Often, the application developer will have already
32 prepared default config files which you can simply "plug and play".
33 But if you <i>need</i> more control over your data storage, you have it,
34 without becoming a programmer.</p>
35
36
37 <a name='providers'><h3>Providers</h3></a>
38
39 <h4>PostgreSQL (psycopg2)</h4>
40 <p>This class was developed against
41     PostgreSQL 8.0.0 rc-1 on Win2k, using psycopg2 version '2.0.5.1 (dec dt ext pq3)'.
42    
43 <ul>
44     <li><b>Short Names:</b> "psycopg"/"psycopg2"
45         (<tt>geniusql.providers.psycopg.PsycoPgDatabase</tt>)</li>
46     <li><b>connections.Connect:</b> A connect string of the form "k=v k=v".
47         For example,
48         <tt>"host=localhost dbname=myapp user=postgres password=hilar1ous"</tt>.
49         See the <a href='http://www.postgresql.org/docs/current/static/libpq.html'>libpq</a>
50         docs for complete information.</li>
51 </ul>
52
53 <h4>MySQL (MySQLdb)</h4>
54 <p>This class was developed against
55     mysql  Ver 14.7 Distrib 4.1.8, for Win95/Win98 (i32),
56     and also tested on
57     mysql  Ver 12.22 Distrib 4.0.23, for pc-linux-gnu (i386).
58    
59 <ul>
60     <li><b>Short Names:</b> "mysql"
61         (<tt>geniusql.providers.mysql.MySQLDatabase</tt>)</li>
62     <li><b>name:</b> The name to use in a CREATE DATABASE statement.</li>
63     <li><tt>connections</tt> arguments: any of "host", "user", "passwd",
64         "db", "port", "unix_socket", "client_flag".<br />See the
65         <a href='http://dev.mysql.com/doc/mysql/en/mysql_real_connect.html'>docs</a>
66         for complete info.</li>
67 </ul>
68
69 <h4>SQLite (sqlite3)</h4>
70 <p>This class was developed against
71     Requires python 2.5 or later.
72     and sqlite 3.3.4 (python 2.5 on win2k).
73 <ul>
74     <li><b>Short Names:</b> "sqlite"
75         (<tt>geniusql.providers.sqlite.SQLiteDatabase</tt>)</li>
76     <li><b>name:</b> Filename of the database. May be a relative path.
77         If the DB does not already exist, it will be created. You may also
78         use the special name <tt>:memory:</tt> to create a database in RAM;
79         however, only one connection may be made to it (multiple connections
80         to <tt>:memory:</tt> open a different DB for each connection).</li>
81     <li><b>Database.mode:</b> Optional. DB file mode. Defaults to 0755.</li>
82 </ul>
83
84
85 <h4>Common Database Attributes</h4>
86 <p>In addition to the above, database providers (probably)
87 accept these additional options:</p>
88
89 <table>
90 <tr><th>Key</th><th>Example Value</th><th>Description</th></tr>
91 <tr>
92     <td>connections.poolsize</td>
93     <td><tt>10</tt></td>
94     <td>Optional. Defaults to 10. If nonzero, connections will be pooled
95         (up to a total equal to <i>poolsize</i>). If zero, no pool
96         will be used; each statement (!) will use a new connection.</td>
97 </tr>
98 <tr>
99     <td>schema.prefix</td>
100     <td><tt>myapp_</tt></td>
101     <td>Optional. If specified, all tables in the database will have names
102     starting with this prefix. If not provided, it defaults to "" (empty).
103     This helps if you need to mix Geniusql tables with tables from another
104     application in the same database. Leave blank if you want no prefix.</td>
105 </tr>
106 <tr>
107     <td>connections.default_isolation</td>
108     <td><tt>"READ COMMITTED"</tt></td>
109     <td>Optional. All database providers already have a value for this,
110         but you can select another if you wish. This value should be a
111         "native value" for your database's particular transaction
112         mechanisms. For example, PostgreSQL uses ANSI/SQL names like
113         "READ COMMITTED", but Firebird uses library constants like
114         <tt>kinterbasdb.isc_tpb_read_committed</tt>.</td>
115 </tr>
116 </table>
117
118
119 <a name='comparison'><h3>Provider Comparison Chart</h3></a>
120
121 <p>When selecting a storage implementation, you should be aware of the
122 strengths and limitations of each option. The following chart should help
123 you decide.</p>
124
125 <p>First, it shows you which stores do and do not support certain
126 optional features of Geniusql. Your application developer should provide
127 you with a list of any features which they <i>require</i>.</p>
128
129 <p>Second, it shows you which stores have performance or boundary issues
130 and where. When developing applications, you should avoid these issues
131 either by coding alternative solutions, or by recommending to your
132 deployers that they avoid the problematic stores. Note that some
133 limitations are inherent in the storage mechanism itself, while some are
134 limitations of the current Geniusql implementation for that mechanism.</p>
135
136 <ul>
137     <li><b>Y</b>: The store supports the feature natively.</li>
138     <li><b>P</b>: The store does not provide the feature natively, but
139         Geniusql provides a fallback in pure Python (which may be slower).
140         Boundaries and limitations are therefore Python limits.</li>
141     <li><b>N</b>: The store does not allow the feature at all.</li>
142     <li>&lt;blank&gt;: Unknown/not yet documented.</li>
143 </ul>
144
145 <table>
146 <tr>
147     <th></th>
148     <th>mysql</th>
149     <th>postgres</th>
150     <th>sqlite</th>
151 </tr>
152
153 <tr>
154     <td>Connection Pool <a href='#connpool'>[5]</a></td>
155     <td class='python'>P</td>
156     <td class='python'>P</td>
157     <td class='python'>P</td>
158 </tr>
159
160 <tr>
161     <td>Transactions</td>
162     <td>Y</td>
163     <td>Y</td>
164     <td>Y</td>
165 </tr>
166
167 <tr>
168     <td>Indexes</td>
169     <td>Y</td>
170     <td>Y</td>
171     <td>Y</td>
172 </tr>
173
174 <tr>
175     <td>Max identifier length</td>
176     <td>64</td>
177     <td>63</td>
178     <td>no limit?</td>
179 </tr>
180
181 <tr>
182     <td>Case-sensitive identifiers</td>
183     <td>Unix only</td>
184     <td>Y</td>
185     <td>Y</td>
186 </tr>
187
188 <tr>
189     <td>Case-sensitive LIKE ("a in b")</td>
190     <td>Y</td>
191     <td>Y</td>
192     <td>Y</td>
193 </tr>
194
195 <tr>
196     <td>Case-sensitive string comparison ("a" &gt; "A")</td>
197     <td>Y</td>
198     <td>Y</td>
199     <td>Y</td>
200 </tr>
201
202 <tr>
203     <td>Wildcard literals in LIKE ("a in b")</td>
204     <td>Y</td>
205     <td>Y</td>
206     <td>3.0.8+</td>
207 </tr>
208
209 <tr>
210     <td>Autoincrement</td>
211     <td>Y</td>
212     <td>Y</td>
213     <td>3.1.0+</td>
214 </tr>
215
216 <tr>
217     <td>add/drop/rename column</td>
218     <td>Y</td>
219     <td>Y</td>
220     <td class='python'>P <a href='#sqlite-alter-table'>[2]</a><br />(add: 3.2.0+)</td>
221 </tr>
222
223 <tr>
224     <th></th>
225     <th>mysql</th>
226     <th>postgres</th>
227     <th>sqlite</th>
228 </tr>
229
230 <tr>
231     <td>fixed point/decimal precision (in decimal digits)</td>
232     <td>16</td>
233     <td>1000</td>
234     <td>0 (always uses TEXT instead)</td>
235 </tr>
236
237 <tr>
238     <td>Max str/unicode bytes</td>
239     <td>8000 (row limit)</td>
240     <td>1 GB?</td>
241     <td>1 MB (row limit)</td>
242 </tr>
243
244 <tr>
245     <td>datetime ranges</td>
246     <td>1000-01-01 00:00:00 to 9999-12-31 23:59:59</td>
247     <td>4713 BC to 5874897 AD</td>
248     <td>4714-11-24 BC to ???</td>
249 </tr>
250
251 <tr>
252     <td>datetime precision</td>
253     <td>1 second</td>
254     <td>1 microsecond</td>
255     <td>1 second</td>
256 </tr>
257
258 <tr>
259     <td>year, month, day functions</td>
260     <td>Y</td>
261     <td>Y</td>
262     <td>3.2.3+ <a href='#perfect-dates'>[1]</a></td>
263 </tr>
264
265 <tr>
266     <td>now, today functions</td>
267     <td>Y</td>
268     <td>Y</td>
269     <td>3.2.3+ <a href='#perfect-dates'>[1]</a></td>
270 </tr>
271
272 <tr>
273     <td>startswith, endswith, containedby,
274         icontainedby, icontains, istartswith, iendswith</td>
275     <td>Y</td>
276     <td>Y</td>
277     <td>Y</td>
278 </tr>
279
280 <tr>
281     <td>builtin function: len</td>
282     <td>Y</td>
283     <td>Y</td>
284     <td>Y</td>
285 </tr>
286
287 <tr>
288     <th></th>
289     <th>mysql</th>
290     <th>postgres</th>
291     <th>sqlite</th>
292 </tr>
293
294 <tr>
295     <td>READ UNCOMMITTED</td>
296     <td>Y</td>
297     <td class='notsup'>N <a href='#too-isolated'>[7]</a></td>
298     <td class='notsup'>N</td>
299 </tr>
300
301 <tr>
302     <td>READ COMMITTED</td>
303     <td>Y</td>
304     <td>Y</td>
305     <td class='notsup'>N</td>
306 </tr>
307 <tr>
308     <td>REPEATABLE READ</td>
309     <td class='notsup'>N <a href='#too-isolated'>[7]</a></td>
310     <td class='notsup'>N <a href='#too-isolated'>[7]</a></td>
311     <td class='notsup'>N</td>
312 </tr>
313 <tr>
314     <td>SERIALIZABLE</td>
315     <td>Y (timeout)</td>
316     <td>Y</td>
317     <td>Y <a href='#memory-trans'>[8]</a></td>
318 </tr>
319 <tr>
320     <td>Change isolation inside transaction</td>
321     <td>Y</td>
322     <td>Y</td>
323     <td class='notsup'>N</td>
324 </tr>
325 </table>
326
327 <p><a name='perfect-dates'>[1]</a> In order to use native date functions in
328 SQLite, you must be storing your date and time values in one of the
329 acceptable formats. See the
330 <a href='http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions'>SQLite wiki</a>
331 for more information. Once you have verified that you are using such a format,
332 you must then set AdapterToSQLite.using_perfect_dates to True. This can be
333 done with the configuration entry: <tt>Perfect Dates: True</tt>.</p>
334
335 <p><a name='sqlite-alter-table'>[2]</a> SQLite must copy the entire table
336 to an intermediate table and then to a new, final table in order to alter
337 tables. Beginning in 3.2.0, adding columns may now be performed natively
338 (but not renaming or dropping them).</p>
339
340 <p><a name='filenames'>[3]</a></p>
341
342 <p><a name='ntext-bytes'>[4]</a> Microsoft SQL Server does not allow
343 comparisons on string fields larger than 8000 characters.</p>
344
345 <p><a name='connpool'>[5]</a> Geniusql provides connection pool factories
346 in pure Python, and does not yet make any attempt to use native pooling
347 features.</p>
348
349 <p><a name='memofields'>[6]</a> Microsoft Access "MEMO" fields have a 1 GB
350 limit, but so does the entire database. Memo fields also cannot be used as
351 join keys; set <tt>hints['bytes'] = 255</tt> or less to use VARCHAR instead.</p>
352
353 <p><a name='memofields'>[7]</a> Some databases over-protect at various
354 isolation levels. For example, "REPEATABLE READ" should prevent fuzzy
355 reads but allow phantoms, but MySQL's and Firebird's REPEATABLE READ
356 prevent both.
357 PostgreSQL only uses two isolation levels internally, so that selecting
358 "READ UNCOMMITTED" behaves like "READ COMMITTED" and "REPEATABLE READ"
359 behaves like "SERIALIZABLE".</p>
360
361 <p><a name='memory-trans'>[8]</a> SQLite <tt>:memory:</tt> databases
362 cannot use multiple connections, so a single connection is used for
363 all threads. However, this means that transactions are generally not
364 allowed for <tt>:memory:</tt> databases when using multiple threads
365 (because multiple transactions would overlap on the same connection
366 and not be isolated at all!).</p>
367
368 </body>
369 </html>
Note: See TracBrowser for help on using the browser.