| 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: Introduction</title> |
|---|
| 8 |
<link href='geniusql.css' rel='stylesheet' type='text/css' /> |
|---|
| 9 |
</head> |
|---|
| 10 |
|
|---|
| 11 |
<body> |
|---|
| 12 |
|
|---|
| 13 |
<h2>Introduction</h2> |
|---|
| 14 |
|
|---|
| 15 |
<p>Geniusql is a thread-safe Object-Relational Mapper for Python applications. |
|---|
| 16 |
It is designed to provide the "Model" third of an MVC application. When you |
|---|
| 17 |
build an application using Geniusql, you must supply the Controller(s) and |
|---|
| 18 |
View(s) yourself. Geniusql does not provide these, and does its best to not |
|---|
| 19 |
limit your choices regarding them.</p> |
|---|
| 20 |
|
|---|
| 21 |
<p>If you're familiar with Martin Fowler's work <a href='#fowler'>[1]</a>, |
|---|
| 22 |
you can think of Geniusql as providing a Data Source layer, plus the tools |
|---|
| 23 |
to write your own Domain layer. For the Presentation layer, you're on your |
|---|
| 24 |
own. ;) It primarily uses a Row Data Gateway architecture.</p> |
|---|
| 25 |
|
|---|
| 26 |
<h3>Basic Structure</h3> |
|---|
| 27 |
<p>Developers build their Model by creating instances of Schema, Table, |
|---|
| 28 |
Column, and Index classes. For OLTP applications, Table objects expose |
|---|
| 29 |
the methods for managing individual rows: insert, select, save, and delete. |
|---|
| 30 |
</p> |
|---|
| 31 |
|
|---|
| 32 |
<p>For OLAP applications, the Database exposes more generic select methods. |
|---|
| 33 |
Table instances can be <i>associated</i> to other Tables. This means |
|---|
| 34 |
that one of the Columns of TableA maps to one of the Columns of TableB. |
|---|
| 35 |
Related data may then be looked up more easily.</p> |
|---|
| 36 |
|
|---|
| 37 |
<p>The results of a select call are usually dicts, where each Column in the |
|---|
| 38 |
result has a corresponding key/value pair in the dict. Complex queries can |
|---|
| 39 |
be written as pure Python lambdas, and Geniusql will decompile them into SQL. |
|---|
| 40 |
</p> |
|---|
| 41 |
|
|---|
| 42 |
|
|---|
| 43 |
<h3>Simple Example</h3> |
|---|
| 44 |
|
|---|
| 45 |
<p>Since a block of code is often worth a thousand words, here's a minimal |
|---|
| 46 |
example of a Geniusql application:</p> |
|---|
| 47 |
|
|---|
| 48 |
zookeeper.py |
|---|
| 49 |
<pre>import geniusql |
|---|
| 50 |
|
|---|
| 51 |
# Set up a global Schema and Database object. |
|---|
| 52 |
conf = {'connections.Connect': |
|---|
| 53 |
r"PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=C:\zookeeper.mdb;"} |
|---|
| 54 |
db = geniusql.db("msaccess", **conf) |
|---|
| 55 |
db.create() |
|---|
| 56 |
schema = db.schema("main") |
|---|
| 57 |
schema.create() |
|---|
| 58 |
|
|---|
| 59 |
Zoo = schema.table('Zoo') |
|---|
| 60 |
Zoo['ID'] = schema.column(int, autoincrement=True, key=True) |
|---|
| 61 |
Zoo.add_index('ID') |
|---|
| 62 |
Zoo['Name'] = schema.column() |
|---|
| 63 |
Zoo['Size'] = schema.column(int) |
|---|
| 64 |
Zoo['Opens'] = schema.column(datetime.time) |
|---|
| 65 |
Zoo['LastEscape'] = schema.column(datetime.datetime) |
|---|
| 66 |
schema['Zoo'] = Zoo |
|---|
| 67 |
|
|---|
| 68 |
Animal = schema.table('Animal') |
|---|
| 69 |
Animal['Legs'] = schema.column(int, default=4) |
|---|
| 70 |
Animal['Name'] = schema.column() |
|---|
| 71 |
Animal['ZooID'] = schema.column(int) |
|---|
| 72 |
Animal.references['Zoo'] = ('ZooID', 'Zoo', 'ID') |
|---|
| 73 |
schema['Animal'] = Animal |
|---|
| 74 |
</pre> |
|---|
| 75 |
|
|---|
| 76 |
<p>The above creates the model for the zookeeper application. |
|---|
| 77 |
There are three basic things happening: |
|---|
| 78 |
<ol> |
|---|
| 79 |
<li>The <tt>Zoo</tt> and <tt>Animal</tt> tables, which are instances |
|---|
| 80 |
of <tt>geniusql.Table</tt>. These will correspond to the Zoo and |
|---|
| 81 |
Animal tables within the database.</li> |
|---|
| 82 |
<li>The association between the Animal class and the Zoo class.</li> |
|---|
| 83 |
<li>The setup of a geniusql <tt>Schema</tt> object, including a |
|---|
| 84 |
<tt>Database</tt> which uses Microsoft Access (Jet).</li> |
|---|
| 85 |
</ol> |
|---|
| 86 |
</p> |
|---|
| 87 |
|
|---|
| 88 |
<p>Here's a simple interactive session which uses the above (assume that |
|---|
| 89 |
tables have been created and populated elsewhere):</p> |
|---|
| 90 |
|
|---|
| 91 |
<pre>>>> import zookeeper |
|---|
| 92 |
>>> Animal.select_all() |
|---|
| 93 |
[{'ID': 1, 'Name': 'Lion', 'Legs': 4, 'ZooID': 1}, |
|---|
| 94 |
{'ID': 2, 'Name': 'Leopard', 'Legs': 4, 'ZooID': 1}, |
|---|
| 95 |
{'ID': 3, 'Name': 'Centipede', 'Legs': 100, 'ZooID': 13}, |
|---|
| 96 |
{'ID': 4, 'Name': 'Slug', 'Legs': 1, 'ZooID': 5}, |
|---|
| 97 |
] |
|---|
| 98 |
>>> Zoo.select_all() |
|---|
| 99 |
[] |
|---|
| 100 |
>>> sdz = Zoo.insert(Name='San Diego Zoo', Size='38') |
|---|
| 101 |
>>> sdz['ID'] |
|---|
| 102 |
1 |
|---|
| 103 |
>>> for creature in Animal.select_all(): |
|---|
| 104 |
Zoo.save(ID=creature['ID'], ZooID=sdz['ID']) |
|---|
| 105 |
>>> len(Animal.select_all(ZooID=sdz['ID'])) |
|---|
| 106 |
4</pre> |
|---|
| 107 |
|
|---|
| 108 |
|
|---|
| 109 |
<h3>Design Goals</h3> |
|---|
| 110 |
|
|---|
| 111 |
<p>Geniusql is designed to function in environments with complex integration |
|---|
| 112 |
needs, and tends to separate concerns as much as possible. In particular, |
|---|
| 113 |
Geniusql tries to avoid making decisions in the framework which are better |
|---|
| 114 |
left to developers. Some of those decisions are: |
|---|
| 115 |
<ul> |
|---|
| 116 |
<li>User interface. Geniusql works well in all sorts of applications, |
|---|
| 117 |
whether desktop, thin-client or web.</li> |
|---|
| 118 |
<li>Application package architecture. You can place your application |
|---|
| 119 |
within a single Python module, develop complete packages, |
|---|
| 120 |
or use Geniusql inside a larger framework.</li> |
|---|
| 121 |
<li>Which types to use for Columns. Builtin types |
|---|
| 122 |
are fully supported out of the box, including datetime and |
|---|
| 123 |
decimal. Tim Peters' excellent <tt>fixedpoint</tt> module |
|---|
| 124 |
is also available. New types are easily added.</li> |
|---|
| 125 |
<li>Which keys to use when associating Tables.</li> |
|---|
| 126 |
<li>What to name identifiers.</li> |
|---|
| 127 |
</ul> |
|---|
| 128 |
|
|---|
| 129 |
In the same way, Geniusql tries to avoid having developers make decisions |
|---|
| 130 |
which are better left to deployers. Some of those decisions are: |
|---|
| 131 |
<ul> |
|---|
| 132 |
<li>Where (and how) to log error messages.</li> |
|---|
| 133 |
<li>Which database(s) to use.</li> |
|---|
| 134 |
</ul> |
|---|
| 135 |
</p> |
|---|
| 136 |
|
|---|
| 137 |
|
|---|
| 138 |
<h3>Obtaining and Installing</h3> |
|---|
| 139 |
|
|---|
| 140 |
<p>You can obtain Geniusql from its Subversion repository at |
|---|
| 141 |
<tt>http://projects.amor.org/geniusql/svn/trunk</tt>. Geniusql is designed |
|---|
| 142 |
to be installed in <tt>site-packages/geniusql</tt> or some other root |
|---|
| 143 |
python path.</p> |
|---|
| 144 |
|
|---|
| 145 |
<p>Geniusql was built using Python 2.4.2. You should probably use |
|---|
| 146 |
at least 2.3; Geniusql depends upon the <tt>datetime</tt> module. |
|---|
| 147 |
Although Geniusql <i>supports</i> additional modules like |
|---|
| 148 |
<tt>fixedpoint</tt> and <tt>decimal</tt>, it does not <i>require</i> |
|---|
| 149 |
them.</p> |
|---|
| 150 |
|
|---|
| 151 |
<p>Geniusql uses bytecode hacks, and therefore requires CPython |
|---|
| 152 |
<a href='#cpython'>[2]</a>.</p> |
|---|
| 153 |
|
|---|
| 154 |
<h3>Compared To Other Database Wrappers</h3> |
|---|
| 155 |
<h4>SQLObject</h4> |
|---|
| 156 |
<p>No matter what project I start on, odds are I'll discover that Ian |
|---|
| 157 |
Bicking has already done the same thing, usually better. |
|---|
| 158 |
<br />See http://blog.ianbicking.org/another-less-sleepy-alternative-to-hibernate.html |
|---|
| 159 |
<br />Which was a reply to Ruby's ActiveRecord: |
|---|
| 160 |
http://www.loudthinking.com/arc/000297.html |
|---|
| 161 |
<br />Which was a reply to Java's Hibernate: |
|---|
| 162 |
http://informit.com/guides/content.asp?g=java&seqNum=127&f1=rss</p> |
|---|
| 163 |
|
|---|
| 164 |
<p>Using Geniusql, the application developer supplies the following code |
|---|
| 165 |
to define the Tables and their relationships:</p> |
|---|
| 166 |
|
|---|
| 167 |
<pre>from geniusql import * |
|---|
| 168 |
import fixedpoint # or decimal, for Python 2.4+ |
|---|
| 169 |
import datetime |
|---|
| 170 |
|
|---|
| 171 |
conf = {'connections.Connect': |
|---|
| 172 |
r"host=localhost dbname=bookstore user=postgres password=****"} |
|---|
| 173 |
db = geniusql.db("postgres", **conf) |
|---|
| 174 |
db.create() |
|---|
| 175 |
schema = db.schema("main") |
|---|
| 176 |
schema.create() |
|---|
| 177 |
|
|---|
| 178 |
Book = schema.table('Book') |
|---|
| 179 |
Book['ID'] = schema.column(int, autoincrement=True, key=True) |
|---|
| 180 |
Book['title'] = schema.column(str) |
|---|
| 181 |
Book['price'] = schema.column(fixedpoint.Fixedpoint) |
|---|
| 182 |
Book['publishDate'] = schema.column(datetime.datetime) |
|---|
| 183 |
Book['publisher'] = schema.column(int) |
|---|
| 184 |
|
|---|
| 185 |
def addAuthor(book, author): |
|---|
| 186 |
Authorship.insert(authorID=author['ID'], bookID=book['ID']) |
|---|
| 187 |
|
|---|
| 188 |
Publisher = schema.table('Publisher') |
|---|
| 189 |
Publisher['ID'] = schema.column(int, autoincrement=True, key=True) |
|---|
| 190 |
Publisher['name'] = schema.column(str) |
|---|
| 191 |
|
|---|
| 192 |
Author = schema.table('Author') |
|---|
| 193 |
Author['ID'] = schema.column(int, autoincrement=True, key=True) |
|---|
| 194 |
Author['name'] = schema.column(str) |
|---|
| 195 |
|
|---|
| 196 |
Authorship = schema.table('Authorship') |
|---|
| 197 |
Authorship['ID'] = schema.column(int, autoincrement=True, key=True) |
|---|
| 198 |
Authorship['authorID'] = schema.column(int) |
|---|
| 199 |
Authorship['bookID'] = schema.column(int) |
|---|
| 200 |
|
|---|
| 201 |
Book.references['Publisher'] = ('publisher', 'Publisher', 'ID') |
|---|
| 202 |
Authorship.references['Book'] = ('bookID', 'Book', 'ID') |
|---|
| 203 |
Authorship.references['Author'] = ('authorID', 'Author', 'ID') |
|---|
| 204 |
</pre> |
|---|
| 205 |
|
|---|
| 206 |
<p>To create the tables, attach them to the schema object:</p> |
|---|
| 207 |
<pre>for t in (Book, Publisher, Author, Authorship): |
|---|
| 208 |
schema[t.name] = t</pre> |
|---|
| 209 |
|
|---|
| 210 |
<p>The app developer's runtime code reads as follows:</p> |
|---|
| 211 |
<pre> |
|---|
| 212 |
ppython = Book.insert(title='Programming Python', price=20, |
|---|
| 213 |
publishDate=datetime.datetime(2001, 3, 1)) |
|---|
| 214 |
print ppython['title'] # output: 'Programming Python' |
|---|
| 215 |
|
|---|
| 216 |
mlutz = Author.insert(name = 'Mark Lutz') |
|---|
| 217 |
addAuthor(ppython, mlutz) |
|---|
| 218 |
|
|---|
| 219 |
print len(Authorship.select_all(bookID=ppython['ID'])) # output: 1 |
|---|
| 220 |
|
|---|
| 221 |
def author_names(book): |
|---|
| 222 |
names = [name for (name, ) |
|---|
| 223 |
in db.select_all(Authorship >> Author, |
|---|
| 224 |
[None, ('name', )], |
|---|
| 225 |
lambda aship, a: aship.bookID == ppython['ID'] |
|---|
| 226 |
)] |
|---|
| 227 |
return u', '.join(names) |
|---|
| 228 |
|
|---|
| 229 |
print author_names(ppython) # output: 'Mark Lutz' |
|---|
| 230 |
|
|---|
| 231 |
oreilly = Publisher.insert(name="O'Reilly") |
|---|
| 232 |
|
|---|
| 233 |
Book.save(ID=ppython['ID'], publisher=oreilly['ID']) |
|---|
| 234 |
print Publisher.select(ID=ppython['publisher'])['name'] # output: "O'Reilly" |
|---|
| 235 |
|
|---|
| 236 |
print len(Book.select_all(authorID=oreilly['ID'])) # output: 1 |
|---|
| 237 |
|
|---|
| 238 |
print 'Hi,', author_names(Book.select(authorID=oreilly['ID'])) # output: "Hi, Mark Lutz" |
|---|
| 239 |
</pre> |
|---|
| 240 |
</p> |
|---|
| 241 |
|
|---|
| 242 |
<hr /> |
|---|
| 243 |
|
|---|
| 244 |
<p><a name='fowler'>[1]</a> Fowler, |
|---|
| 245 |
<a href='http://www.martinfowler.com/eaaCatalog/identityMap.html'>Patterns |
|---|
| 246 |
of Enterprise Application Architecture</a>.<br /> |
|---|
| 247 |
<a name='cpython'>[2]</a> Geniusql relies upon bytecode hacking to achieve |
|---|
| 248 |
its clean lambda syntax for data queries. Therefore, it is CPython-specific. |
|---|
| 249 |
In addition, the bytecode of Python may change from one version of Python |
|---|
| 250 |
to another; if you find your version of Python does not work with Geniusql's |
|---|
| 251 |
<tt>codewalk</tt> and <tt>logic</tt> modules, please let me know.<br /> |
|---|
| 252 |
</p> |
|---|
| 253 |
|
|---|
| 254 |
</body> |
|---|
| 255 |
</html> |
|---|
