"Providers" insulate an application developer from the specifics of databases. As the deployer of a Geniusql application, you get to be in control of these specifics. But don't worry; in the vast majority of cases, you will set up a single database with just two lines in a configuration file. Often, the application developer will have already prepared default config files which you can simply "plug and play". But if you need more control over your data storage, you have it, without becoming a programmer.
This class was developed against PostgreSQL 8.0.0 rc-1 on Win2k, using psycopg2 version '2.0.5.1 (dec dt ext pq3)'.
This class was developed against mysql Ver 14.7 Distrib 4.1.8, for Win95/Win98 (i32), and also tested on mysql Ver 12.22 Distrib 4.0.23, for pc-linux-gnu (i386).
This class was developed against Requires python 2.5 or later. and sqlite 3.3.4 (python 2.5 on win2k).
In addition to the above, database providers (probably) accept these additional options:
| Key | Example Value | Description |
|---|---|---|
| connections.poolsize | 10 | Optional. Defaults to 10. If nonzero, connections will be pooled (up to a total equal to poolsize). If zero, no pool will be used; each statement (!) will use a new connection. |
| schema.prefix | myapp_ | Optional. If specified, all tables in the database will have names starting with this prefix. If not provided, it defaults to "" (empty). This helps if you need to mix Geniusql tables with tables from another application in the same database. Leave blank if you want no prefix. |
| connections.default_isolation | "READ COMMITTED" | Optional. All database providers already have a value for this, but you can select another if you wish. This value should be a "native value" for your database's particular transaction mechanisms. For example, PostgreSQL uses ANSI/SQL names like "READ COMMITTED", but Firebird uses library constants like kinterbasdb.isc_tpb_read_committed. |
When selecting a storage implementation, you should be aware of the strengths and limitations of each option. The following chart should help you decide.
First, it shows you which stores do and do not support certain optional features of Geniusql. Your application developer should provide you with a list of any features which they require.
Second, it shows you which stores have performance or boundary issues and where. When developing applications, you should avoid these issues either by coding alternative solutions, or by recommending to your deployers that they avoid the problematic stores. Note that some limitations are inherent in the storage mechanism itself, while some are limitations of the current Geniusql implementation for that mechanism.
| mysql | postgres | sqlite | |
|---|---|---|---|
| Connection Pool [5] | P | P | P |
| Transactions | Y | Y | Y |
| Indexes | Y | Y | Y |
| Max identifier length | 64 | 63 | no limit? |
| Case-sensitive identifiers | Unix only | Y | Y |
| Case-sensitive LIKE ("a in b") | Y | Y | Y |
| Case-sensitive string comparison ("a" > "A") | Y | Y | Y |
| Wildcard literals in LIKE ("a in b") | Y | Y | 3.0.8+ |
| Autoincrement | Y | Y | 3.1.0+ |
| add/drop/rename column | Y | Y | P [2] (add: 3.2.0+) |
| mysql | postgres | sqlite | |
| fixed point/decimal precision (in decimal digits) | 16 | 1000 | 0 (always uses TEXT instead) |
| Max str/unicode bytes | 8000 (row limit) | 1 GB? | 1 MB (row limit) |
| datetime ranges | 1000-01-01 00:00:00 to 9999-12-31 23:59:59 | 4713 BC to 5874897 AD | 4714-11-24 BC to ??? |
| datetime precision | 1 second | 1 microsecond | 1 second |
| year, month, day functions | Y | Y | 3.2.3+ [1] |
| now, today functions | Y | Y | 3.2.3+ [1] |
| startswith, endswith, containedby, icontainedby, icontains, istartswith, iendswith | Y | Y | Y |
| builtin function: len | Y | Y | Y |
| mysql | postgres | sqlite | |
| READ UNCOMMITTED | Y | N [7] | N |
| READ COMMITTED | Y | Y | N |
| REPEATABLE READ | N [7] | N [7] | N |
| SERIALIZABLE | Y (timeout) | Y | Y [8] |
| Change isolation inside transaction | Y | Y | N |
[1] In order to use native date functions in SQLite, you must be storing your date and time values in one of the acceptable formats. See the SQLite wiki for more information. Once you have verified that you are using such a format, you must then set AdapterToSQLite.using_perfect_dates to True. This can be done with the configuration entry: Perfect Dates: True.
[2] SQLite must copy the entire table to an intermediate table and then to a new, final table in order to alter tables. Beginning in 3.2.0, adding columns may now be performed natively (but not renaming or dropping them).
[4] Microsoft SQL Server does not allow comparisons on string fields larger than 8000 characters.
[5] Geniusql provides connection pool factories in pure Python, and does not yet make any attempt to use native pooling features.
[6] Microsoft Access "MEMO" fields have a 1 GB limit, but so does the entire database. Memo fields also cannot be used as join keys; set hints['bytes'] = 255 or less to use VARCHAR instead.
[7] Some databases over-protect at various isolation levels. For example, "REPEATABLE READ" should prevent fuzzy reads but allow phantoms, but MySQL's and Firebird's REPEATABLE READ prevent both. PostgreSQL only uses two isolation levels internally, so that selecting "READ UNCOMMITTED" behaves like "READ COMMITTED" and "REPEATABLE READ" behaves like "SERIALIZABLE".
[8] SQLite :memory: databases cannot use multiple connections, so a single connection is used for all threads. However, this means that transactions are generally not allowed for :memory: databases when using multiple threads (because multiple transactions would overlap on the same connection and not be isolated at all!).