Project

General

Profile

Model » History » Version 14

Elmer de Looff, 2012-04-27 17:29

1 1 Elmer de Looff
h1. Database abstraction model
2 1 Elmer de Looff
3 1 Elmer de Looff
h2. Goal of this component
4 1 Elmer de Looff
5 1 Elmer de Looff
The µWeb framework provides a @model@ module with the intention of simplifying database access. The design goal is to provide a rich abstraction that
6 1 Elmer de Looff
* takes away the tedious work of retrieving, creating and deleting records
7 1 Elmer de Looff
* can load its parent objects automatically if so required
8 1 Elmer de Looff
* _does *not* get in the way of the developer_
9 1 Elmer de Looff
10 1 Elmer de Looff
Making database interaction easier without restricting the abilities of the developer is our main goal. Some default mechanisms make assumptions on the way the database is organised, but these are well-documented, and it's entirely possible to change the behavior of these mechanisms.
11 1 Elmer de Looff
12 12 Elmer de Looff
h1. model.Record
13 1 Elmer de Looff
14 2 Elmer de Looff
The basic idea of the @Record@ class is that it is a container for your database records, with related records automatically loaded as needed, and custom methods that provide more info, child objects, etc. Outlined below are the default features available, with minimal configuration requirements.
15 1 Elmer de Looff
16 12 Elmer de Looff
h2. Basic Record usage
17 1 Elmer de Looff
18 2 Elmer de Looff
There are a few ways to use the @Record@ class. The direct way to create a @Record@ is to initiate it with a connection, and a dictionary of @field -> value@ information. The @Record@ is a dictionary subclass that largely copies all the functionality of a dictionary. Retrieving values for keys works exactly as you'd expect.
19 1 Elmer de Looff
20 12 Elmer de Looff
h2. Creating your own @Record@
21 1 Elmer de Looff
22 2 Elmer de Looff
To create your own @Record@ subclass, nothing is required beyond the class name. The following example substitutes a complete working example:
23 1 Elmer de Looff
<pre><code class="python">
24 13 Jan Klopper
from uweb import model
25 2 Elmer de Looff
class Message(model.Record):
26 2 Elmer de Looff
  """Abstraction class for messages stored in the database."""
27 2 Elmer de Looff
</code></pre>
28 1 Elmer de Looff
29 12 Elmer de Looff
h2. Primary field definition
30 1 Elmer de Looff
31 2 Elmer de Looff
The @Record@ requires that a table has a single-field unique column. It's advisable for this to be a PRIMARY index in the database, though this is not required. This field is used to automatically look up a record if it is referenced and requested elsewhere.
32 1 Elmer de Looff
33 2 Elmer de Looff
By default, this primary key field is assumed to be @ID@. If this is not the case for your table, you can easily change this by defining the @_PRIMARY_KEY@ class constant:
34 1 Elmer de Looff
35 2 Elmer de Looff
<pre><code class="python">
36 13 Jan Klopper
from uweb import model
37 2 Elmer de Looff
class Country(model.Record):
38 2 Elmer de Looff
  """Abstraction class for a country table.
39 1 Elmer de Looff
40 2 Elmer de Looff
  This class uses the ISO-3166-1 alpha2 country code as primary key.
41 2 Elmer de Looff
  """
42 2 Elmer de Looff
  _PRIMARY_KEY = 'alpha2'
43 2 Elmer de Looff
</code></pre>
44 1 Elmer de Looff
45 12 Elmer de Looff
h2. Class and table relation
46 2 Elmer de Looff
47 1 Elmer de Looff
By default, the assumption is made that the table name is the same as the class name, with the first letter lowercase. *The table related to the class @Message@ would be @message@.* To change this behavior, assign the correct table name to the @_TABLE@ class constant. This new table name will then be used in all built-in Record methods:
48 2 Elmer de Looff
49 2 Elmer de Looff
<pre><code class="python">
50 13 Jan Klopper
from uweb import model
51 2 Elmer de Looff
class Message(model.Record):
52 2 Elmer de Looff
  """Abstraction class for messages stored in the database."""
53 2 Elmer de Looff
  _TABLE = 'MyMessage'
54 2 Elmer de Looff
</code></pre>
55 2 Elmer de Looff
56 12 Elmer de Looff
h2. Record initialization
57 1 Elmer de Looff
58 6 Elmer de Looff
Initializing a Record object requires a database connection as first argument, and a dictionary with the record's data as second argument. This second argument can, alternatively, be an iterator of key+value tuples.
59 1 Elmer de Looff
60 6 Elmer de Looff
<pre><code class="python">
61 13 Jan Klopper
from uweb import model
62 6 Elmer de Looff
class Message(model.Record):
63 6 Elmer de Looff
  """Abstraction class for messages stored in the database."""
64 1 Elmer de Looff
65 6 Elmer de Looff
# Caller side:
66 6 Elmer de Looff
>>> record = {'ID': 1, 'message': 'First message!', 'author': 'Elmer'}
67 7 Elmer de Looff
>>> message = Message(db_conn, record)
68 6 Elmer de Looff
>>> print message
69 6 Elmer de Looff
Message({'message': 'First message!', 'ID': 1, 'author': 'Elmer'})
70 6 Elmer de Looff
</code></pre>
71 1 Elmer de Looff
72 6 Elmer de Looff
This basic construction is rarely needed in code using the Record objects, but is important for alternative initializers, of which one is provided by default:
73 6 Elmer de Looff
74 12 Elmer de Looff
h2. Alternative initializer: create Record from primary key
75 6 Elmer de Looff
76 1 Elmer de Looff
On the caller side, it's impractical to first query the database, and then instantiate a Record subclass from that. Alternative initializers provide a solution without requiring module-level functions that have poor cohesion to the relevant class. Alternative initializers are @classmethods@, working not on instance, but aiming to create and return one.
77 6 Elmer de Looff
78 6 Elmer de Looff
There is one such alternative initializer provided: @FromKey@, which loads a record from the database based on its primary key. Required for this to function are two arguments: A database connection, and the value for the primary key field:
79 6 Elmer de Looff
80 6 Elmer de Looff
<pre><code class="python">
81 13 Jan Klopper
from uweb import model
82 6 Elmer de Looff
class Message(model.Record):
83 6 Elmer de Looff
  """Abstraction class for messages stored in the database."""
84 6 Elmer de Looff
85 6 Elmer de Looff
# Caller side:
86 7 Elmer de Looff
>>> message = Message.FromKey(db_conn, 1)
87 6 Elmer de Looff
>>> print message
88 6 Elmer de Looff
Message({'message': u'First message!', 'ID': 1L, 'author': 'Elmer'})
89 1 Elmer de Looff
# Unicode and long integer are side effects from the database read, not the Record class
90 1 Elmer de Looff
</code></pre>
91 7 Elmer de Looff
92 12 Elmer de Looff
h2. On-demand loading of referenced records.
93 7 Elmer de Looff
94 7 Elmer de Looff
In databases that are more complex than a single table, information is often normalized. That is, the author information in our previously demonstrated *message* table will be stored in a separate *author* table. The author field on message records will be a _reference_ to a record in the author table.
95 7 Elmer de Looff
96 7 Elmer de Looff
Consider the following tables in your database:
97 7 Elmer de Looff
<pre><code class="html">
98 7 Elmer de Looff
-- TABLE `message`
99 7 Elmer de Looff
+----+--------+--------------------------------------------------+
100 7 Elmer de Looff
| ID | author | message                                          |
101 7 Elmer de Looff
+----+--------+--------------------------------------------------+
102 7 Elmer de Looff
|  1 |      1 | First message!                                   |
103 7 Elmer de Looff
|  2 |      2 | Robert'); DROP TABLE Students;--                 |
104 7 Elmer de Looff
|  3 |      1 | You didn't think it would be this easy, did you? |
105 7 Elmer de Looff
+----+--------+--------------------------------------------------+
106 7 Elmer de Looff
107 7 Elmer de Looff
-- TABLE `author`
108 7 Elmer de Looff
+----+-------+--------------------+
109 7 Elmer de Looff
| ID | name  | emailAddress       |
110 7 Elmer de Looff
+----+-------+--------------------+
111 7 Elmer de Looff
|  1 | Elmer | elmer@underdark.nl |
112 7 Elmer de Looff
|  2 | Bobby | bobby@tables.com   |
113 1 Elmer de Looff
+----+-------+--------------------+
114 7 Elmer de Looff
</code></pre>
115 7 Elmer de Looff
116 7 Elmer de Looff
And the following class definitions in Python:
117 7 Elmer de Looff
118 7 Elmer de Looff
<pre><code class="python">
119 13 Jan Klopper
from uweb import model
120 7 Elmer de Looff
class Author(model.Record):
121 7 Elmer de Looff
  """Abstraction class for author records."""
122 7 Elmer de Looff
123 7 Elmer de Looff
class Message(model.Record):
124 7 Elmer de Looff
  """Abstraction class for messages records."""
125 7 Elmer de Looff
</code></pre>
126 7 Elmer de Looff
127 7 Elmer de Looff
This makes it possible to retrieve a message, and from that Message object, retrieve the author information. This is done when the information is requested, and not pre-loaded beforehand. This means that retrieving a thousand Message objects will *not* trigger an additional 1000 queries to retrieve the author information, if that information might not be used at all.
128 7 Elmer de Looff
129 7 Elmer de Looff
<pre><code class="python">
130 7 Elmer de Looff
>>> message = Message.FromKey(db_connection, 1)
131 7 Elmer de Looff
>>> message
132 7 Elmer de Looff
Message({'message': u'First message!', 'ID': 1L, 'author': 1})
133 7 Elmer de Looff
# This is the same message we saw before, without author information.
134 7 Elmer de Looff
# However, retrieving the author field specifically, provides its record:
135 7 Elmer de Looff
>>> message['author']
136 7 Elmer de Looff
Author({'emailAddress': u'elmer@underdark.nl', 'ID': 1, 'name': u'Elmer'})
137 7 Elmer de Looff
>>> message
138 7 Elmer de Looff
Message({'message': u'First message!', 'ID': 1L,
139 7 Elmer de Looff
         'author': Author({'emailAddress': u'elmer@underdark.nl', 'ID': 1, 'name': u'Elmer'})})
140 7 Elmer de Looff
</code></pre>
141 7 Elmer de Looff
142 7 Elmer de Looff
This works on the assumption that *any field name, that is also the table name of another Record class, is a reference to that record*. In the case of the example above: The message table contains a field _author_. There exists a Record subclass for that table (namely _Author_, table 'author'). The value of @message['author']@ (1), is now used to load an Author record using the FromKey alternative initializer, with _1_ as the primary key value.
143 7 Elmer de Looff
144 7 Elmer de Looff
# @message['author']@ uses the _author_ field
145 7 Elmer de Looff
# _author_ table is abstracted by Author class
146 1 Elmer de Looff
# @message['author']@ is replaced by @Author.FromKey(db_connection, message['author']@
147 7 Elmer de Looff
148 8 Elmer de Looff
This behavior can be modified using the _FOREIGN_RELATIONS class constant. This provides a mapping that specifies (and overrides) which Record classes should be used to resolve references from fields. The key for the mapping is a field name (string), and the corresponding value is a class or None. None specifies that the field does *not* represent a reference, and should be used as-is. Classes may be given as string because at the time of evaluation, not all classes exist, and attempting using a class directly might result in a NameError. Without this provision, the order of classes would be dictated by the model, and cross-references would not be possible at all.
149 1 Elmer de Looff
150 8 Elmer de Looff
An example case for a situation where the table names are plural, but the field names are singular:
151 8 Elmer de Looff
152 8 Elmer de Looff
<pre><code class="python">
153 13 Jan Klopper
from uweb import model
154 8 Elmer de Looff
class Author(model.Record):
155 8 Elmer de Looff
  """Abstraction class for author records."""
156 8 Elmer de Looff
  _TABLE = 'authors'
157 8 Elmer de Looff
158 1 Elmer de Looff
class Message(model.Record):
159 1 Elmer de Looff
  """Abstraction class for messages records."""
160 1 Elmer de Looff
  _TABLE = 'messages'
161 1 Elmer de Looff
  _FOREIGN_RELATIONS = {'author': Author}
162 1 Elmer de Looff
</code></pre>
163 1 Elmer de Looff
164 12 Elmer de Looff
h2. Loading child objects (1-to-n relations)
165 1 Elmer de Looff
166 9 Elmer de Looff
The model provides a generic method to retrieve child records (that is, _1 to n_ relations) of a Record. The desired relations _should_ have an associated Record class. The method to use is @_GetChildren@, which is a private method of any Record class. As its argument, it needs the name of a child class. Returned is an iterator that yields instances of the given Record subclass. 
167 9 Elmer de Looff
168 9 Elmer de Looff
Given its name and usage, the suggested usage of this is to wrap a more descriptive method around this:
169 9 Elmer de Looff
170 9 Elmer de Looff
<pre><code class="python">
171 13 Jan Klopper
from uweb import model
172 9 Elmer de Looff
class Author(model.Record):
173 9 Elmer de Looff
  """Abstraction class for author records."""
174 9 Elmer de Looff
  def Messages(self):
175 9 Elmer de Looff
    """Returns an iterator for all messages written by this author."""
176 9 Elmer de Looff
    return self._GetChildren(Message)
177 9 Elmer de Looff
178 9 Elmer de Looff
class Message(model.Record):
179 9 Elmer de Looff
  """Abstraction class for messages records."""
180 9 Elmer de Looff
181 9 Elmer de Looff
# Caller code
182 9 Elmer de Looff
>>> elmer = Author.FromKey(db_connection, 1)
183 9 Elmer de Looff
>>> for message in elmer.Messages():
184 9 Elmer de Looff
...   print message
185 9 Elmer de Looff
Message({'message': u'First message!', 'ID': 1L,
186 9 Elmer de Looff
         'author': Author({'emailAddress': u'elmer@underdark.nl', 'ID': 1, 'name': u'Elmer'})})
187 9 Elmer de Looff
Message({'message': u"You didn't think it would be this easy, did you?", 'ID': 3L,
188 9 Elmer de Looff
         'author': Author({'emailAddress': u'elmer@underdark.nl', 'ID': 1, 'name': u'Elmer'})})
189 9 Elmer de Looff
# Reflowing to keep things legible
190 9 Elmer de Looff
</code></pre>
191 9 Elmer de Looff
192 9 Elmer de Looff
What you can see here is that all messages written by the given author are retrieved from the database, and presented. This is done with a single database query, where the _child_ Record's table is searched for rows where the @relation_field@ is equal to the parent Record's primary key value. This @relation_field@ is an optional argument to the @_GetChildren@ method, and defaults to the class' table name.
193 11 Elmer de Looff
194 9 Elmer de Looff
*N.B. @print@ and the methods @(iter)items@, @(iter)values@ all cause the object's foreign relations to be retrieved.*
195 9 Elmer de Looff
196 9 Elmer de Looff
An example with pluralized table names:
197 9 Elmer de Looff
198 9 Elmer de Looff
<pre><code class="python">
199 9 Elmer de Looff
class Author(model.Record):
200 9 Elmer de Looff
  """Abstraction class for author records."""
201 9 Elmer de Looff
  _TABLE = 'authors'
202 9 Elmer de Looff
203 9 Elmer de Looff
  def Messages(self):
204 9 Elmer de Looff
    """Returns an iterator for all messages written by this author."""
205 9 Elmer de Looff
    return self._GetChildren(Message, relation_field='author')
206 9 Elmer de Looff
207 9 Elmer de Looff
class Message(model.Record):
208 9 Elmer de Looff
  """Abstraction class for messages records."""
209 9 Elmer de Looff
  _TABLE = 'messages'
210 9 Elmer de Looff
  _FOREIGN_RELATIONS = {'author': Author}
211 1 Elmer de Looff
</code></pre>
212 10 Elmer de Looff
213 12 Elmer de Looff
h2. Retrieving all records
214 11 Elmer de Looff
215 10 Elmer de Looff
For situations where all records must be retrieved or processed, there is the @List@ classmethod, that takes a single connection argument. This returns an iterator for all Record objects of the type it's called for:
216 10 Elmer de Looff
217 10 Elmer de Looff
<pre><code class="python">
218 10 Elmer de Looff
class Message(model.Record):
219 10 Elmer de Looff
  """Abstraction class for messages records."""
220 10 Elmer de Looff
221 10 Elmer de Looff
# List all messages:
222 10 Elmer de Looff
>>> for message in Message.List(db_connection):
223 10 Elmer de Looff
...   print message
224 10 Elmer de Looff
... 
225 10 Elmer de Looff
Message({'message': u'First message!', 'ID': 1L, 'author': 1})
226 10 Elmer de Looff
Message({'message': u"Robert'); DROP TABLE Students;--", 'ID': 2L, 'author': 2})
227 10 Elmer de Looff
Message({'message': u"You didn't think it would be this easy, did you?", 'ID': 3L, 'author': 1})
228 10 Elmer de Looff
</code></pre>
229 10 Elmer de Looff
230 10 Elmer de Looff
*N.B.*: If the Author class were defined here, it would be automatically loaded where the primary key for the author is now listed. This has been omitted in this example for reasons of brevity and readability.
231 10 Elmer de Looff
232 12 Elmer de Looff
h2. Updating a record
233 10 Elmer de Looff
234 10 Elmer de Looff
After loading a record, it can be altered, and saved. These changes (and optionally changes to nested records), will be committed to the database, and reflected in the current loaded record.
235 10 Elmer de Looff
236 10 Elmer de Looff
<pre><code class="python">
237 10 Elmer de Looff
class Author(model.Record):
238 10 Elmer de Looff
  """Abstraction class for author records."""
239 10 Elmer de Looff
240 10 Elmer de Looff
class Message(model.Record):
241 10 Elmer de Looff
  """Abstraction class for messages records."""
242 10 Elmer de Looff
243 10 Elmer de Looff
>>> retort = Message.FromKey(db_connection, 3)
244 10 Elmer de Looff
>>> retort['message'] = "Please go away Bobby."
245 10 Elmer de Looff
>>> # Our changes are not yet reflected in the database:
246 10 Elmer de Looff
>>> print Message.FromKey(db_connection, 3)
247 10 Elmer de Looff
Message({'message': u"You didn't think it would be this easy, did you?", 'ID': 3L,
248 10 Elmer de Looff
         'author': Author({'emailAddress': u'elmer@underdark.nl', 'ID': 1, 'name': u'Elmer'})})
249 10 Elmer de Looff
>>> retort.Save()
250 10 Elmer de Looff
>>> # Now our changes are committed to the database:
251 10 Elmer de Looff
>>> print Message.FromKey(db_connection, 3)
252 10 Elmer de Looff
Message({'message': u'Please go away Bobby.', 'ID': 3L,
253 10 Elmer de Looff
         'author': Author({'emailAddress': u'elmer@underdark.nl', 'ID': 1, 'name': u'Elmer'})})
254 10 Elmer de Looff
</code></pre>
255 10 Elmer de Looff
256 10 Elmer de Looff
If we specify *save_foreign* as _True_, we can also alter the information stored in foreign relations, and have that saved in the same operation. This way we could alter both the author name, or email address, as well as the message itself.
257 10 Elmer de Looff
258 12 Elmer de Looff
h2. Adding a record
259 10 Elmer de Looff
260 10 Elmer de Looff
Using the same @Save@ method, we can also add records to the database. This can be done either with the Primary Key given, or left undefined. If the key is left undefined (or defined as None), the Record will assume that the primary key field is an auto-increment field, and insert data in that manner.
261 10 Elmer de Looff
262 10 Elmer de Looff
*N.B.* Skipping fields that are optional in the database is allowed, but their default values assigned by the database will _not_ be reflected in the object. That is, the record will not be reloaded after storing.
263 10 Elmer de Looff
264 10 Elmer de Looff
Creating a record using an auto-incrementing primary key:
265 10 Elmer de Looff
<pre><code class="python">
266 10 Elmer de Looff
class Message(model.Record):
267 10 Elmer de Looff
  """Abstraction class for messages records."""
268 10 Elmer de Looff
269 10 Elmer de Looff
>>> new_message = Message(db_connection, {'author': 1, 'message': 'A new message, should be #4'})
270 10 Elmer de Looff
>>> new_message.Save()
271 10 Elmer de Looff
>>> new_message.key
272 10 Elmer de Looff
4L
273 10 Elmer de Looff
>>> print Message.FromKey(db_connection, new_message.key)
274 10 Elmer de Looff
Message({'message': u'A new message, should be #4', 'ID': 4L, 'author': 1})
275 10 Elmer de Looff
</code></pre>
276 10 Elmer de Looff
277 10 Elmer de Looff
Creating a record where we specify the key:
278 10 Elmer de Looff
<pre><code class="python">
279 10 Elmer de Looff
class Message(model.Record):
280 10 Elmer de Looff
  """Abstraction class for messages records."""
281 10 Elmer de Looff
282 10 Elmer de Looff
>>> another_message = Message(db_connection, {})
283 10 Elmer de Looff
>>> another_message.key = 6  # we could assign to the 'ID' key as well
284 10 Elmer de Looff
>>> another_message['author'] = 2
285 10 Elmer de Looff
>>> another_message['message'] = 'Creating a message with a defined primary key value'
286 10 Elmer de Looff
>>> another_message.Save()
287 10 Elmer de Looff
4L
288 10 Elmer de Looff
>>> print Message.FromKey(db_connection, another_message.key)
289 10 Elmer de Looff
Message({'message': u'Creating a message with a defined primary key value', 'ID': 6L, 'author': 2})
290 10 Elmer de Looff
</code></pre>
291 10 Elmer de Looff
292 12 Elmer de Looff
h2. Deleting a record
293 10 Elmer de Looff
294 10 Elmer de Looff
Records can be deleted from the database either from a loaded object, or using the DeleteKey classmethod. This latter removes the record from the database using the primary key to select it.
295 10 Elmer de Looff
296 10 Elmer de Looff
<pre><code class="python">
297 10 Elmer de Looff
class Message(model.Record):
298 10 Elmer de Looff
  """Abstraction class for messages records."""
299 10 Elmer de Looff
300 10 Elmer de Looff
# Loading and deleting an active record.
301 10 Elmer de Looff
>>> bad_record = Message.FromKey(db_connection, 3)
302 10 Elmer de Looff
>>> bad_record.Delete()
303 10 Elmer de Looff
304 10 Elmer de Looff
# Deleting a record based on its primary key.
305 10 Elmer de Looff
>>> Message.DeleteKey(db_connection, 2)
306 10 Elmer de Looff
</code></pre>
307 10 Elmer de Looff
308 12 Elmer de Looff
h2. Equality comparison
309 10 Elmer de Looff
310 10 Elmer de Looff
Records must pass the following criteria to be considered equal to one another.:
311 10 Elmer de Looff
# *Type*: Two objects must be of the same type (class)
312 10 Elmer de Looff
# *Primary key*: The primary key values must compare equal
313 10 Elmer de Looff
# *Foreign relations*: Foreign relations must be the same. If these are not resolved in one object but are in the other, the primary key of the resolved object will be compared to the data of the other record.
314 10 Elmer de Looff
# *Data*: All remaining data fields must be equal and symmetric (i.e. both objects describe the same fields)
315 10 Elmer de Looff
316 14 Elmer de Looff
h3. Greater / smaller comparisons
317 10 Elmer de Looff
318 10 Elmer de Looff
Comparing two objects with one another to tell their relative order can _only_ be done if they are of the same type. If they're the same type, the comparison result that of the two primary keys for those records.