1
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
2
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
4
<html xmlns="http://www.w3.org/1999/xhtml">
6
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
8
<title>MVCSQL — openerp v1 documentation</title>
9
<link rel="stylesheet" href="../_static/default.css" type="text/css" />
10
<link rel="stylesheet" href="../_static/pygments.css" type="text/css" />
11
<script type="text/javascript">
12
var DOCUMENTATION_OPTIONS = {
15
COLLAPSE_MODINDEX: false,
20
<script type="text/javascript" src="../_static/jquery.js"></script>
21
<script type="text/javascript" src="../_static/doctools.js"></script>
22
<link rel="top" title="openerp v1 documentation" href="../index.html" />
23
<link rel="up" title="OpenObject Architecture - MVC" href="index.html" />
24
<link rel="next" title="First steps" href="../part_1/index.html" />
25
<link rel="prev" title="MVC - Model, View, Controller" href="mvc.html" />
31
<li class="right" style="margin-right: 10px">
32
<a href="../genindex.html" title="General Index"
33
accesskey="I">index</a></li>
35
<a href="../part_1/index.html" title="First steps"
36
accesskey="N">next</a> |</li>
38
<a href="mvc.html" title="MVC - Model, View, Controller"
39
accesskey="P">previous</a> |</li>
40
<li><a href="../index.html">openerp v1 documentation</a> »</li>
41
<li><a href="index.html" accesskey="U">OpenObject Architecture - MVC</a> »</li>
45
<div class="document">
46
<div class="documentwrapper">
47
<div class="bodywrapper">
50
<div class="section" id="mvcsql">
51
<h1>MVCSQL<a class="headerlink" href="#mvcsql" title="Permalink to this headline">¶</a></h1>
52
<div class="section" id="example-1">
53
<h2>Example 1<a class="headerlink" href="#example-1" title="Permalink to this headline">¶</a></h2>
54
<p>Suppose sale is a variable on a record of the sale.order object related to the ‘sale_order’ table. You can acquire such a variable doing this.:</p>
55
<div class="highlight-python"><div class="highlight"><pre><span class="n">sale</span> <span class="o">=</span> <span class="bp">self</span><span class="o">.</span><span class="n">browse</span><span class="p">(</span><span class="n">cr</span><span class="p">,</span> <span class="n">uid</span><span class="p">,</span> <span class="n">ID</span><span class="p">)</span>
58
<p>(where cr is the current row, from the database cursor, uid is the current user’s ID for security checks, and ID is the sale order’s ID or list of IDs if we want more than one)</p>
59
<p>Suppose you want to get: the country name of the first contact of a partner related to the ID sale order. You can do the following in Open ERP:</p>
60
<div class="highlight-python"><div class="highlight"><pre><span class="n">country_name</span> <span class="o">=</span> <span class="n">sale</span><span class="o">.</span><span class="n">partner_id</span><span class="o">.</span><span class="n">address</span><span class="p">[</span><span class="mf">0</span><span class="p">]</span><span class="o">.</span><span class="n">country_id</span><span class="o">.</span><span class="n">name</span>
63
<p>If you want to write the same thing in traditional SQL development, it will be in python: (we suppose cr is the cursor on the database, with psycopg)</p>
64
<div class="highlight-python"><div class="highlight"><pre><span class="n">cr</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">'select partner_id from sale_order where id=</span><span class="si">%d</span><span class="s">'</span><span class="p">,</span> <span class="p">(</span><span class="n">ID</span><span class="p">,))</span>
65
<span class="n">partner_id</span> <span class="o">=</span> <span class="n">cr</span><span class="o">.</span><span class="n">fetchone</span><span class="p">()[</span><span class="mf">0</span><span class="p">]</span>
66
<span class="n">cr</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">'select country_id from res_partner_address where partner_id=</span><span class="si">%d</span><span class="s">'</span><span class="p">,</span> <span class="p">(</span><span class="n">partner_id</span><span class="p">,))</span>
67
<span class="n">country_id</span> <span class="o">=</span> <span class="n">cr</span><span class="o">.</span><span class="n">fetchone</span><span class="p">()[</span><span class="mf">0</span><span class="p">]</span>
68
<span class="n">cr</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">'select name from res_country where id=</span><span class="si">%d</span><span class="s">'</span><span class="p">,</span> <span class="p">(</span><span class="n">country_id</span><span class="p">,))</span>
69
<span class="k">del</span> <span class="n">partner_id</span>
70
<span class="k">del</span> <span class="n">country_id</span>
71
<span class="n">country_name</span> <span class="o">=</span> <span class="n">cr</span><span class="o">.</span><span class="n">fetchone</span><span class="p">()[</span><span class="mf">0</span><span class="p">]</span>
74
<p>Of course you can do better if you develop smartly in SQL, using joins or subqueries. But you have to be smart and most of the time you will not be able to make such improvements:</p>
77
<li>Maybe some parts are in others functions</li>
78
<li>There may be a loop in different elements</li>
79
<li>You have to use intermediate variables like country_id</li>
82
<p>The first operation as an object call is much better for several reasons:</p>
85
<li>It uses objects facilities and works with modules inheritances, overload, ...</li>
86
<li>It’s simpler, more explicit and uses less code</li>
87
<li>It’s much more efficient as you will see in the following examples</li>
88
<li>Some fields do not directly correspond to a SQL field (e.g.: function fields in Python)</li>
92
<div class="section" id="example-2-prefetching">
93
<h2>Example 2 - Prefetching<a class="headerlink" href="#example-2-prefetching" title="Permalink to this headline">¶</a></h2>
94
<p>Suppose that later in the code, in another function, you want to access the name of the partner associated to your sale order. You can use this:</p>
95
<div class="highlight-python"><div class="highlight"><pre><span class="n">partner_name</span> <span class="o">=</span> <span class="n">sale</span><span class="o">.</span><span class="n">partner_id</span><span class="o">.</span><span class="n">name</span>
98
<p>And this will not generate any SQL query as it has been prefetched by the object relational mapping engine of Open ERP.</p>
100
<div class="section" id="loops-and-special-fields">
101
<h2>Loops and special fields<a class="headerlink" href="#loops-and-special-fields" title="Permalink to this headline">¶</a></h2>
102
<p>Suppose now that you want to compute the totals of 10 sales order by countries. You can do this in Open ERP within a Open ERP object:</p>
103
<div class="highlight-python"><div class="highlight"><pre><span class="k">def</span> <span class="nf">get_totals</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">cr</span><span class="p">,</span> <span class="n">uid</span><span class="p">,</span> <span class="n">ids</span><span class="p">):</span>
104
<span class="n">countries</span> <span class="o">=</span> <span class="p">{}</span>
105
<span class="k">for</span> <span class="n">sale</span> <span class="ow">in</span> <span class="bp">self</span><span class="o">.</span><span class="n">browse</span><span class="p">(</span><span class="n">cr</span><span class="p">,</span> <span class="n">uid</span><span class="p">,</span> <span class="n">ids</span><span class="p">):</span>
106
<span class="n">country</span> <span class="o">=</span> <span class="n">sale</span><span class="o">.</span><span class="n">partner_invoice_id</span><span class="o">.</span><span class="n">country</span>
107
<span class="n">countries</span><span class="o">.</span><span class="n">setdefault</span><span class="p">(</span><span class="n">country</span><span class="p">,</span> <span class="mf">0.0</span><span class="p">)</span>
108
<span class="n">countries</span><span class="p">[</span><span class="n">country</span><span class="p">]</span> <span class="o">+=</span> <span class="n">sale</span><span class="o">.</span><span class="n">amount_untaxed</span>
109
<span class="k">return</span> <span class="n">countries</span>
112
<p>And, to print them as a good way, you can add this on your object:</p>
113
<div class="highlight-python"><div class="highlight"><pre><span class="k">def</span> <span class="nf">print_totals</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">cr</span><span class="p">,</span> <span class="n">uid</span><span class="p">,</span> <span class="n">ids</span><span class="p">):</span>
114
<span class="n">result</span> <span class="o">=</span> <span class="bp">self</span><span class="o">.</span><span class="n">get_totals</span><span class="p">(</span><span class="n">cr</span><span class="p">,</span> <span class="n">uid</span><span class="p">,</span> <span class="n">ids</span><span class="p">)</span>
115
<span class="k">for</span> <span class="n">country</span> <span class="ow">in</span> <span class="n">result</span><span class="o">.</span><span class="n">keys</span><span class="p">():</span>
116
<span class="k">print</span> <span class="s">'[</span><span class="si">%s</span><span class="s">] </span><span class="si">%s</span><span class="s">: </span><span class="si">%.2f</span><span class="s">'</span> <span class="p">(</span><span class="n">country</span><span class="o">.</span><span class="n">code</span><span class="p">,</span> <span class="n">country</span><span class="o">.</span><span class="n">name</span><span class="p">,</span> <span class="n">result</span><span class="p">[</span><span class="n">country</span><span class="p">])</span>
119
<p>The 2 functions will generate 4 SQL queries in total ! This is due to the SQL engine of Open ERP that does prefetching, works on lists and uses caching methods. The 3 queries are:</p>
121
<ol class="arabic simple">
122
<li>Reading the sale.order to get ID’s of the partner’s address</li>
123
<li>Reading the partner’s address for the countries</li>
124
<li>Calling the amount_untaxed function that will compute a total of the sale order lines</li>
125
<li>Reading the countries info (code and name)</li>
128
<p>That’s great because if you run this code on 1000 sales orders, you have the guarantee to only have 4 SQL queries.</p>
132
<li><p class="first">IDS is the list of the 10 ID’s: [12,15,18,34, ...,99]</p>
134
<li><dl class="first docutils">
135
<dt>The arguments of a function are always the same:</dt>
136
<dd><ul class="first last">
137
<li><dl class="first docutils">
138
<dt>cr: the cursor database (from psycopg)</dt>
139
<dd><ul class="first last simple">
140
<li>uid: the user id (for security checks)</li>
149
<li><p class="first">If you run this code on 5000 sales orders, you may have 8 SQL queries because as SQL queries are not allowed to take too much memory, it may have to do two separate readings.</p>
154
<div class="section" id="a-complete-example">
155
<h2>A complete example<a class="headerlink" href="#a-complete-example" title="Permalink to this headline">¶</a></h2>
156
<p>Here is a complete example, from the Open ERP official distribution, of the function that does bill of material explosion and computation of associated routings:</p>
157
<div class="highlight-python"><pre>class mrp_bom(osv.osv):
159
def _bom_find(self, cr, uid, product_id, product_uom, properties=[]):
161
# Why searching on `BoM without parent ?
162
cr.execute('select id from mrp_bom where product_id=%d and bom_id is null
163
order by sequence', (product_id,))
164
ids = map(lambda x: x[0], cr.fetchall())
167
for bom in self.pool.get('mrp.bom').browse(cr, uid, ids):
169
for prop_id in bom.property_ids:
170
if prop_id.id in properties:
172
if (prop>max_prop) or ((max_prop==0) and not result):
176
def _bom_explode(self, cr, uid, bom, factor, properties, addthis=False, level=10):
177
factor = factor / (bom.product_efficiency or 1.0)
178
factor = rounding(factor, bom.product_rounding)
179
if factor<bom.product_rounding:
180
factor = bom.product_rounding
183
if bom.type=='phantom' and not bom.bom_lines:
184
newbom = self._bom_find(cr, uid, bom.product_id.id,
185
bom.product_uom.id, properties)
187
res = self._bom_explode(cr, uid, self.browse(cr, uid, [newbom])[0],
188
factor*bom.product_qty, properties, addthis=True, level=level+10)
189
result = result + res[0]
190
result2 = result2 + res[1]
194
if addthis and not bom.bom_lines:
197
'name': bom.product_id.name,
198
'product_id': bom.product_id.id,
199
'product_qty': bom.product_qty * factor,
200
'product_uom': bom.product_uom.id,
203
for wc_use in bom.routing_id.workcenter_lines:
204
wc = wc_use.workcenter_id
205
cycle = factor * wc_use.cycle_nbr
207
'name': bom.routing_id.name,
208
'workcenter_id': wc.id,
211
'hour': wc_use.hour_nbr + (
212
wc.time_start+wc.time_stop+cycle*wc.time_cycle) *
213
(wc.time_efficiency or 1
215
for bom2 in bom.bom_lines:
216
res = self._bom_explode(cr, uid, bom2, factor, properties,
217
addthis=True, level=level+10)
218
result = result + res[0]
219
result2 = result2 + res[1]
220
return result, result2</pre>
229
<div class="sphinxsidebar">
230
<div class="sphinxsidebarwrapper">
231
<h3><a href="../index.html">Table Of Contents</a></h3>
233
<li><a class="reference external" href="">MVCSQL</a><ul>
234
<li><a class="reference external" href="#example-1">Example 1</a></li>
235
<li><a class="reference external" href="#example-2-prefetching">Example 2 - Prefetching</a></li>
236
<li><a class="reference external" href="#loops-and-special-fields">Loops and special fields</a></li>
237
<li><a class="reference external" href="#a-complete-example">A complete example</a></li>
242
<h4>Previous topic</h4>
243
<p class="topless"><a href="mvc.html"
244
title="previous chapter">MVC - Model, View, Controller</a></p>
246
<p class="topless"><a href="../part_1/index.html"
247
title="next chapter">First steps</a></p>
249
<ul class="this-page-menu">
250
<li><a href="../_sources/architecture/mvc_sql.txt"
251
rel="nofollow">Show Source</a></li>
253
<div id="searchbox" style="display: none">
254
<h3>Quick search</h3>
255
<form class="search" action="../search.html" method="get">
256
<input type="text" name="q" size="18" />
257
<input type="submit" value="Go" />
258
<input type="hidden" name="check_keywords" value="yes" />
259
<input type="hidden" name="area" value="default" />
261
<p class="searchtip" style="font-size: 90%">
262
Enter search terms or a module, class or function name.
265
<script type="text/javascript">$('#searchbox').show(0);</script>
268
<div class="clearer"></div>
270
<div class="related">
273
<li class="right" style="margin-right: 10px">
274
<a href="../genindex.html" title="General Index"
277
<a href="../part_1/index.html" title="First steps"
280
<a href="mvc.html" title="MVC - Model, View, Controller"
282
<li><a href="../index.html">openerp v1 documentation</a> »</li>
283
<li><a href="index.html" >OpenObject Architecture - MVC</a> »</li>
287
© Copyright 2009, openerp.
288
Created using <a href="http://sphinx.pocoo.org/">Sphinx</a> 0.6.1.
b'\\ No newline at end of file'
added
removed
build/html/architecture/mvc_sql.html
