Project

General

Profile

Model » History » Version 24

Jan Klopper, 2012-05-31 17:17

1 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
2 1 Elmer de Looff
* takes away the tedious work of retrieving, creating and deleting records
3 1 Elmer de Looff
* can load its parent objects automatically if so required
4 1 Elmer de Looff
* _does *not* get in the way of the developer_
5 1 Elmer de Looff
6 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.
7 1 Elmer de Looff
8 15 Elmer de Looff
{{toc}}
9 15 Elmer de Looff
10 15 Elmer de Looff
h1. Record
11 1 Elmer de Looff
12 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.
13 1 Elmer de Looff
14 22 Elmer de Looff
h2. Your first @Record@ class
15 1 Elmer de Looff
16 21 Elmer de Looff
To create your own @Record@ subclass, nothing is required beyond the class' name. The following example substitutes a complete working example:
17 1 Elmer de Looff
<pre><code class="python">
18 1 Elmer de Looff
from uweb import model
19 13 Jan Klopper
class Message(model.Record):
20 1 Elmer de Looff
  """Abstraction class for messages stored in the database."""
21 2 Elmer de Looff
</code></pre>
22 1 Elmer de Looff
23 22 Elmer de Looff
h2. Loading fields from primary key
24 1 Elmer de Looff
25 22 Elmer de Looff
The Record class comes loaded with a way to load records from your database using the @FromPrimary@ method. This is a classmethod available on the @Record@ class and all your own subclasses, and when given a connection and primary key value, will load that record from the database. Provided you have a database that looks like this:
26 1 Elmer de Looff
27 22 Elmer de Looff
<pre><code class="html">
28 22 Elmer de Looff
-- TABLE `message`
29 22 Elmer de Looff
+----+--------+--------------------------------------------------+
30 22 Elmer de Looff
| ID | author | message                                          |
31 22 Elmer de Looff
+----+--------+--------------------------------------------------+
32 22 Elmer de Looff
|  1 | Elmer  | First message!                                   |
33 22 Elmer de Looff
|  2 | Bobby  | Robert'); DROP TABLE Students;--                 |
34 22 Elmer de Looff
|  3 | Elmer  | You didn't think it would be this easy, did you? |
35 22 Elmer de Looff
+----+--------+--------------------------------------------------+
36 22 Elmer de Looff
</code></pre>
37 1 Elmer de Looff
38 22 Elmer de Looff
You can load data from this table with the following code:
39 22 Elmer de Looff
40 1 Elmer de Looff
<pre><code class="python">
41 22 Elmer de Looff
# The model:
42 1 Elmer de Looff
from uweb import model
43 22 Elmer de Looff
class Message(model.Record):
44 22 Elmer de Looff
  """Abstraction class for messages stored in the database."""
45 22 Elmer de Looff
46 22 Elmer de Looff
# Using this:
47 22 Elmer de Looff
>>> message = Message.FromPrimary(db_conn, 1)
48 22 Elmer de Looff
>>> print message
49 22 Elmer de Looff
Message({'message': u'First message!', 'ID': 1L, 'author': u'Elmer'})
50 22 Elmer de Looff
</code></pre>
51 22 Elmer de Looff
52 22 Elmer de Looff
h3. Changing the primary key field
53 22 Elmer de Looff
54 22 Elmer de Looff
By default, @Record@ uses a primary key called @'ID'@. You can change this to any value you like, and @FromPrimary@ will automatically work based on that value, and all other methods and functionality of the class will also use this new definition (deleting, creating and auto-loading from related tables, which are all explained later).
55 22 Elmer de Looff
56 22 Elmer de Looff
To change the primary key field, create a class with a defined @_PRIMARY_KEY@ class variable:
57 22 Elmer de Looff
<pre><code class="python">
58 22 Elmer de Looff
from uweb import model
59 1 Elmer de Looff
class Country(model.Record):
60 6 Elmer de Looff
  """Abstraction class for a country table.
61 1 Elmer de Looff
62 1 Elmer de Looff
  This class uses the ISO-3166-1 alpha2 country code as primary key.
63 1 Elmer de Looff
  """
64 1 Elmer de Looff
  _PRIMARY_KEY = 'alpha2'
65 1 Elmer de Looff
</code></pre>
66 1 Elmer de Looff
67 22 Elmer de Looff
h3. Compound primary keys
68 1 Elmer de Looff
69 22 Elmer de Looff
The µWeb model also supports compound primary keys, with one limitation: @AUTO_INCREMENT@ fields are not supported for creation of the Record, all values need to be provided for it.
70 1 Elmer de Looff
71 24 Jan Klopper
Loading values from a compound primary key works by passing a tuple instead of a single value:
72 22 Elmer de Looff
73 1 Elmer de Looff
<pre><code class="python">
74 22 Elmer de Looff
# The model:
75 1 Elmer de Looff
from uweb import model
76 22 Elmer de Looff
class MonthReport(model.Record):
77 22 Elmer de Looff
  """Abstraction class for the monthReport table.
78 22 Elmer de Looff
79 22 Elmer de Looff
  This is keyed on a composite of both year and month, foregoing the need for a separate AUTO_INCREMENT field.
80 22 Elmer de Looff
  """
81 22 Elmer de Looff
  _PRIMARY_KEY = 'year', 'month'
82 22 Elmer de Looff
83 22 Elmer de Looff
# Using this:
84 22 Elmer de Looff
>>> report = MonthReport.FromPrimary(db_conn, (2012, 5))
85 22 Elmer de Looff
>>> print report
86 22 Elmer de Looff
Message({'report': 'Things went really well', 'month': 5, 'year': 2012})
87 1 Elmer de Looff
</code></pre>
88 1 Elmer de Looff
89 22 Elmer de Looff
h3. Class and table relation
90 1 Elmer de Looff
91 22 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 your own table name to the @_TABLE@ class constant. This new table name will then be used in all built-in Record methods:
92 1 Elmer de Looff
93 1 Elmer de Looff
<pre><code class="python">
94 1 Elmer de Looff
from uweb import model
95 1 Elmer de Looff
class Message(model.Record):
96 1 Elmer de Looff
  """Abstraction class for messages stored in the database."""
97 22 Elmer de Looff
  _TABLE = 'MyMessage'
98 22 Elmer de Looff
</code></pre>
99 1 Elmer de Looff
100 22 Elmer de Looff
Alternatively, you can override the @TableName@ class-method to alter the table-name transformation that is done.
101 22 Elmer de Looff
102 22 Elmer de Looff
h2. Creating records
103 22 Elmer de Looff
104 22 Elmer de Looff
To create a record in the database, you can use the classmethod @Create@. This takes the connection and a dictionary of the keys and values that should be inserted into the database. Using the @Message@ class we defined earlier, creating a new record is a relatively simple call:
105 22 Elmer de Looff
106 22 Elmer de Looff
<pre><code class="python">
107 22 Elmer de Looff
>>> message = Message.Create(db_conn, {'author': 'Bob', 'message': 'Another message'})
108 1 Elmer de Looff
>>> print message
109 22 Elmer de Looff
Message({'message': 'Another message', 'ID': 4L, 'author': 'Bob'})
110 1 Elmer de Looff
</code></pre>
111 1 Elmer de Looff
112 23 Jan Klopper
*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.
113 1 Elmer de Looff
114 22 Elmer de Looff
h2. Deleting records
115 6 Elmer de Looff
116 22 Elmer de Looff
Records can be deleted from the database either from a loaded object, or using the @DeletePrimary@ classmethod. This latter removes the record from the database using the primary key to select it.
117 6 Elmer de Looff
118 22 Elmer de Looff
<pre><code class="python">
119 22 Elmer de Looff
class Message(model.Record):
120 22 Elmer de Looff
  """Abstraction class for messages records."""
121 16 Elmer de Looff
122 22 Elmer de Looff
# Loading and deleting an active record.
123 22 Elmer de Looff
>>> bad_record = Message.FromPrimary(db_connection, 3)
124 22 Elmer de Looff
>>> bad_record.Delete()
125 22 Elmer de Looff
126 22 Elmer de Looff
# Deleting a record based on its primary key.
127 22 Elmer de Looff
>>> Message.DeletePrimary(db_connection, 2)
128 22 Elmer de Looff
</code></pre>
129 22 Elmer de Looff
130 22 Elmer de Looff
h2. Listing all records
131 22 Elmer de Looff
132 22 Elmer de Looff
For situations where all records must be retrieved or processed, there is the @List@ classmethod. This takes the connection as argument and iterates over all records in the database:
133 22 Elmer de Looff
134 12 Elmer de Looff
<pre><code class="python">
135 7 Elmer de Looff
class Message(model.Record):
136 22 Elmer de Looff
  """Abstraction class for messages records."""
137 7 Elmer de Looff
138 22 Elmer de Looff
# List all messages:
139 22 Elmer de Looff
>>> for message in Message.List(db_connection):
140 22 Elmer de Looff
...   print message
141 22 Elmer de Looff
... 
142 22 Elmer de Looff
Message({'message': u'First message!', 'ID': 1L, 'author': 1})
143 22 Elmer de Looff
Message({'message': u"Robert'); DROP TABLE Students;--", 'ID': 2L, 'author': 2})
144 22 Elmer de Looff
Message({'message': u"You didn't think it would be this easy, did you?", 'ID': 3L, 'author': 1})
145 7 Elmer de Looff
</code></pre>
146 7 Elmer de Looff
147 7 Elmer de Looff
h2. On-demand loading of referenced records.
148 7 Elmer de Looff
149 22 Elmer de Looff
In databases that are more complex than a single table (nearly ''all''), 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.
150 7 Elmer de Looff
151 7 Elmer de Looff
Consider the following tables in your database:
152 7 Elmer de Looff
<pre><code class="html">
153 1 Elmer de Looff
-- TABLE `message`
154 7 Elmer de Looff
+----+--------+--------------------------------------------------+
155 7 Elmer de Looff
| ID | author | message                                          |
156 1 Elmer de Looff
+----+--------+--------------------------------------------------+
157 7 Elmer de Looff
|  1 |      1 | First message!                                   |
158 7 Elmer de Looff
|  2 |      2 | Robert'); DROP TABLE Students;--                 |
159 7 Elmer de Looff
|  3 |      1 | You didn't think it would be this easy, did you? |
160 7 Elmer de Looff
+----+--------+--------------------------------------------------+
161 13 Jan Klopper
162 7 Elmer de Looff
-- TABLE `author`
163 7 Elmer de Looff
+----+-------+--------------------+
164 1 Elmer de Looff
| ID | name  | emailAddress       |
165 7 Elmer de Looff
+----+-------+--------------------+
166 1 Elmer de Looff
|  1 | Elmer | elmer@underdark.nl |
167 7 Elmer de Looff
|  2 | Bobby | bobby@tables.com   |
168 1 Elmer de Looff
+----+-------+--------------------+
169 1 Elmer de Looff
</code></pre>
170 7 Elmer de Looff
171 1 Elmer de Looff
And the following class definitions in Python:
172 1 Elmer de Looff
173 1 Elmer de Looff
<pre><code class="python">
174 1 Elmer de Looff
from uweb import model
175 1 Elmer de Looff
class Author(model.Record):
176 7 Elmer de Looff
  """Abstraction class for author records."""
177 7 Elmer de Looff
178 16 Elmer de Looff
class Message(model.Record):
179 7 Elmer de Looff
  """Abstraction class for messages records."""
180 8 Elmer de Looff
</code></pre>
181 1 Elmer de Looff
182 8 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.
183 8 Elmer de Looff
184 8 Elmer de Looff
<pre><code class="python">
185 22 Elmer de Looff
>>> message = Message.FromPrimary(db_conn, 1)
186 8 Elmer de Looff
>>> message
187 8 Elmer de Looff
Message({'message': u'First message!', 'ID': 1L, 'author': 1})
188 8 Elmer de Looff
# This is the same message we saw before, without author information.
189 1 Elmer de Looff
# However, retrieving the author field specifically, provides its record:
190 8 Elmer de Looff
>>> message['author']
191 1 Elmer de Looff
Author({'emailAddress': u'elmer@underdark.nl', 'ID': 1, 'name': u'Elmer'})
192 1 Elmer de Looff
>>> message
193 1 Elmer de Looff
Message({'message': u'First message!', 'ID': 1L,
194 1 Elmer de Looff
         'author': Author({'emailAddress': u'elmer@underdark.nl', 'ID': 1, 'name': u'Elmer'})})
195 1 Elmer de Looff
</code></pre>
196 1 Elmer de Looff
197 22 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 table*. 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 FromPrimary classmethod, with @1@ as the primary key value.
198 10 Elmer de Looff
199 10 Elmer de Looff
# @message['author']@ uses the _author_ field
200 22 Elmer de Looff
# _author_ table is represented by Author class
201 10 Elmer de Looff
# @message['author']@ is replaced by @Author.FromPrimary(db_connection, message['author']@
202 10 Elmer de Looff
203 22 Elmer de Looff
h3. Customize table-references
204 10 Elmer de Looff
205 22 Elmer de Looff
The auto-loading 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 can be a class or @None@.
206 10 Elmer de Looff
207 22 Elmer de Looff
* @None@ specifies that the field does *not* represent a reference, and should be used as-is.
208 22 Elmer de Looff
* 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@. This "class as string" exception only exists for classes that are defined in the same module, and exists so that the model does not force you to define your classes in a certain order. It also enables the case where two tables cross-reference eachother.
209 22 Elmer de Looff
210 22 Elmer de Looff
The following is an example case where the table names are plural, but the field names are singular:
211 22 Elmer de Looff
212 10 Elmer de Looff
<pre><code class="python">
213 10 Elmer de Looff
from uweb import model
214 10 Elmer de Looff
class Author(model.Record):
215 12 Elmer de Looff
  """Abstraction class for author records."""
216 10 Elmer de Looff
  _TABLE = 'authors'
217 10 Elmer de Looff
218 1 Elmer de Looff
class Message(model.Record):
219 10 Elmer de Looff
  """Abstraction class for messages records."""
220 10 Elmer de Looff
  _TABLE = 'messages'
221 10 Elmer de Looff
  _FOREIGN_RELATIONS = {'author': Author}
222 10 Elmer de Looff
</code></pre>
223 10 Elmer de Looff
224 10 Elmer de Looff
h2. Loading child objects (1-to-n relations)
225 10 Elmer de Looff
226 22 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 @_Children@, 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. 
227 16 Elmer de Looff
228 10 Elmer de Looff
Given its name and usage, the suggested usage of this is to wrap a more descriptive method around this:
229 10 Elmer de Looff
230 16 Elmer de Looff
<pre><code class="python">
231 10 Elmer de Looff
from uweb import model
232 10 Elmer de Looff
class Author(model.Record):
233 10 Elmer de Looff
  """Abstraction class for author records."""
234 10 Elmer de Looff
  def Messages(self):
235 16 Elmer de Looff
    """Returns an iterator for all messages written by this author."""
236 10 Elmer de Looff
    return self._Children(Message)
237 10 Elmer de Looff
238 10 Elmer de Looff
class Message(model.Record):
239 10 Elmer de Looff
  """Abstraction class for messages records."""
240 10 Elmer de Looff
241 10 Elmer de Looff
# Caller code
242 12 Elmer de Looff
>>> elmer = Author.FromPrimary(db_connection, 1)
243 10 Elmer de Looff
>>> for message in elmer.Messages():
244 10 Elmer de Looff
...   print message
245 10 Elmer de Looff
Message({'message': u'First message!', 'ID': 1L,
246 10 Elmer de Looff
         'author': Author({'emailAddress': u'elmer@underdark.nl', 'ID': 1, 'name': u'Elmer'})})
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
# Reflowing to keep things legible
250 10 Elmer de Looff
</code></pre>
251 10 Elmer de Looff
252 10 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 @_Children@ method, and defaults to the class' table name.
253 10 Elmer de Looff
254 10 Elmer de Looff
*N.B. @print@ and the methods @(iter)items@, @(iter)values@ all cause the object's foreign relations to be retrieved.*
255 10 Elmer de Looff
256 22 Elmer de Looff
The same example, this time with pluralized table names:
257 16 Elmer de Looff
258 10 Elmer de Looff
<pre><code class="python">
259 10 Elmer de Looff
class Author(model.Record):
260 10 Elmer de Looff
  """Abstraction class for author records."""
261 10 Elmer de Looff
  _TABLE = 'authors'
262 10 Elmer de Looff
263 10 Elmer de Looff
  def Messages(self):
264 10 Elmer de Looff
    """Returns an iterator for all messages written by this author."""
265 10 Elmer de Looff
    return self._Children(Message, relation_field='author')
266 10 Elmer de Looff
267 10 Elmer de Looff
class Message(model.Record):
268 10 Elmer de Looff
  """Abstraction class for messages records."""
269 10 Elmer de Looff
  _TABLE = 'messages'
270 10 Elmer de Looff
  _FOREIGN_RELATIONS = {'author': Author}
271 10 Elmer de Looff
</code></pre>
272 16 Elmer de Looff
273 10 Elmer de Looff
h2. Updating a record
274 10 Elmer de Looff
275 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.
276 12 Elmer de Looff
277 10 Elmer de Looff
<pre><code class="python">
278 10 Elmer de Looff
class Author(model.Record):
279 10 Elmer de Looff
  """Abstraction class for author records."""
280 10 Elmer de Looff
281 10 Elmer de Looff
class Message(model.Record):
282 10 Elmer de Looff
  """Abstraction class for messages records."""
283 10 Elmer de Looff
284 10 Elmer de Looff
>>> retort = Message.FromPrimary(db_connection, 3)
285 16 Elmer de Looff
>>> retort['message'] = "Please go away Bobby."
286 10 Elmer de Looff
>>> # Our changes are not yet reflected in the database:
287 10 Elmer de Looff
>>> print Message.FromPrimary(db_connection, 3)
288 10 Elmer de Looff
Message({'message': u"You didn't think it would be this easy, did you?", 'ID': 3L,
289 10 Elmer de Looff
         'author': Author({'emailAddress': u'elmer@underdark.nl', 'ID': 1, 'name': u'Elmer'})})
290 10 Elmer de Looff
>>> retort.Save()
291 1 Elmer de Looff
>>> # Now our changes are committed to the database:
292 19 Elmer de Looff
>>> print Message.FromPrimary(db_connection, 3)
293 19 Elmer de Looff
Message({'message': u'Please go away Bobby.', 'ID': 3L,
294 20 Elmer de Looff
         'author': Author({'emailAddress': u'elmer@underdark.nl', 'ID': 1, 'name': u'Elmer'})})
295 1 Elmer de Looff
</code></pre>
296 1 Elmer de Looff
297 22 Elmer de Looff
To save all changes in related fields, we can provide the named argument *save_foreign* and set it to _True_. This way we could alter both the author name and the message itself in one database transaction.
298 12 Elmer de Looff
299 10 Elmer de Looff
h2. Comparisons
300 10 Elmer de Looff
301 10 Elmer de Looff
h3. Equality
302 20 Elmer de Looff
303 10 Elmer de Looff
Records must pass the following criteria to be considered equal to one another.:
304 18 Elmer de Looff
# *Type*: Two objects must be of the same type (class)
305 16 Elmer de Looff
# *Primary key*: The primary key values must compare equal
306 16 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.
307 16 Elmer de Looff
# *Data*: All remaining data fields must be equal and symmetric (i.e. both objects describe the same fields)
308 16 Elmer de Looff
309 1 Elmer de Looff
h3. Greater / smaller
310 1 Elmer de Looff
311 1 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 are, the comparison is done based on the primary key values of the records. In most cases this will result in an ordering similar to the database-insert order.
312 1 Elmer de Looff
313 1 Elmer de Looff
h1. VersionedRecord
314 1 Elmer de Looff
315 1 Elmer de Looff
h1. MongoRecord