DEV Community: Khaled Said

Beyond Unique Constraints in Odoo

Khaled Said — Wed, 08 May 2024 19:15:07 +0000

Inspired by

PostgreSQL exclusion constraints: Beyond UNIQUE ~ by Hans-Jürgen Schönig

In Odoo platform you could apply model constraints in two methods. the first one is using SQL constraints and the second is using Python. Odoo provide Python constraints to give you the capability to apply checks on sophisticated logic.

An example of applying unique using SQL constraints in Odoo framework by adding _sql_constraints class attribute.

_sql_constraints = [
    (
        'code_company_uniq',
        'unique (code,company_id)',
        'The code of the account must be unique per company !'
    )
]

code snippet example

such simple constrains could be applied using python logic as following:

@api.constrains("code", "company_id")
def _uniqe_code_per_company(self):
    records = self._read_group(
        domain=[("code", "=", self.code), ("company_id", "=", self.company_id.id)],
        fields=["company_id", "ids:array_agg(id)"],
        groupby=["company_id"],
    )
    for rec in records:
        if rec["company_id_count"] >= 2:
            raise ValidationError(_("The code of the account must be unique per company !"))

code snippet example

as it appears the Python code is more than the SQL code. in a different scenario you may need to check uniqueness between ranges data-structures. like dates cannot be overlapped, applying such scenario using Python would require a lot of logic. luckily, we could use Postgresql exclusion constraints in such case. imagine you have the following model.

KPI NAME	Bottom Percentage	Top Percentage
Sales Target Achieved	85%	95%
Sales Target Achieved	95%	105%
Sales Target Achieved	105%	125%
Sales Target Achieved	125%	150%

and the model definition in Odoo is as following:

class SalesKPIPercentage(models.Model):
    _name = "sales.kpi.percentage"

    type_kpi_id = fields.Many2one("sales.kpi.type", string="KPI Name", required=True)
    percent_bottom = fields.Float(
        string="Bottom Percentage",
        required=True,
    )
    percent_top = fields.Float(
        string="Top Percentage",
        required=True,
    )

to apply a constraints so no records to have overlapped percentage with the same type. you could use SQL contraints usign GIST as following:

_sql_constraints = [
    (
        "no_overlapped_kpis",
        """
            EXCLUDE USING GIST (
                numrange(percent_bottom::numeric, percent_top::numeric) WITH &&,
                int4range(type_kpi_id, type_kpi_id, '[]') WITH =
            )
        """,
        "KPI category Overlapped",
    ),
]

please note that you could also compare against fields.Char definition. forexample if the field definition of type_kpi_id is of type fields.Selection as:

type_kpi_id = fields.Many2one([
    ('sale_target_amount', 'Sales Target Amount'),
    ('sale_target_qty', 'Sales Target Quantity'),
], string="KPI Name", required=True)

the SQL constraints would be as:

_sql_constraints = [
    (
        "no_overlapped_kpis",
        """
            EXCLUDE USING GIST (
                numrange(percent_bottom::numeric, percent_top::numeric) WITH &&,
                type_kpi_id WITH =
            )
        """,
        "KPI category Overlapped",
    ),
]

and in such case you will need to enable a postgresql btree_gist extension using a pre_init_hook as:

def add_pg_extensions(cr, registry):
    cr.execute("CREATE EXTENSION IF NOT EXISTS btree_gist")

Adjust Odoo Error Codes with monkey patching

Khaled Said — Sat, 29 Jul 2023 07:34:12 +0000

In API integration scenario, Odoo error codes may not be satisfactory. In Odoo the default error code of json request is 200. you could handle such case by overriding _handle_exception method found in the following path: https://github.com/odoo/odoo/blob/9d6095da7b2508193ce42cc951ffdd2d3adf5856/odoo/http.py#L649

an example of such code would be as following:

import logging
import werkzeug

import odoo
from odoo.http import AuthenticationError, JsonRequest, serialize_exception, SessionExpiredException

_logger = logging.getLogger(__name__)


def _handle_exception(self, exception):
    """Called within an except block to allow converting exceptions
       to arbitrary responses. Anything returned (except None) will
       be used as response."""
    try:
        return super(JsonRequest, self)._handle_exception(exception)
    except Exception:
        if not isinstance(exception, SessionExpiredException):
            if exception.args and exception.args[0] == "bus.Bus not available in test mode":
                _logger.info(exception)
            elif isinstance(exception, (odoo.exceptions.UserError,
                                        werkzeug.exceptions.NotFound)):
                _logger.warning(exception)
            else:
                _logger.exception("Exception during JSON request handling.")
        error = {
            'code': 400,
            'message': "Odoo Server Error",
            'data': serialize_exception(exception),
        }
        if isinstance(exception, werkzeug.exceptions.NotFound):
            error['http_status'] = 404
            error['code'] = 404
            error['message'] = "404: Not Found"
        if isinstance(exception, AuthenticationError):
            error['code'] = 100
            error['message'] = "Odoo Session Invalid"
        if isinstance(exception, SessionExpiredException):
            error['code'] = 100
            error['message'] = "Odoo Session Expired"
        if isinstance(exception, werkzeug.exceptions.UnprocessableEntity):
            error['code'] = 422
            error['message'] = "422 Unprocessable Entity"
        return self._json_response(error=error)


JsonRequest._handle_exception = _handle_exception

you could see in line 40 that you could add a new error code which is in Odoo before. also note line 46 which apply the monkey patching. so you have to make sure that your module is loaded before any module overriding such method by adjusting module dependency.

Advanced usage of map & filter in Python

Khaled Said — Thu, 27 Jul 2023 16:44:32 +0000

In 2 different job interviews I got asked to solve the following question using python. so I thought it would be good to publish the solution for the question.

You have the following relation:

  erDiagram
    AttendanceActions ||--o{ Attendance : AttendanceId
    AttendanceActions {
        int id
        int AttendanceId
        datetime ActionTime
        text Action
    }
    Attendance {
        int id
        date day
        text employee
    }

and the following tables:

Attendance

id	day	employee
1	2020-04-01	EMP01
2	2020-04-01	EMP02
3	2020-04-02	EMP01
4	2020-04-03	EMP01
5	2020-04-02	EMP02
6	2020-04-03	EMP02

AttendanceActions

id	AttendanceId	ActionTime	Action
1	1	2020-04-01 12:00 AM	CheckIn
2	1	2020-04-01 12:00 PM	CheckOut
3	2	2020-04-01 12:05 PM	CheckIn
4	2	2020-04-01 11:50 PM	CheckOut
5	3	2020-04-02 12:05 AM	CheckIn
6	3	2020-04-02 11:50 AM	CheckOut
7	3	2020-04-02 11:50 PM	CheckIn
8	4	2020-04-03 12:05 PM	CheckOut

Overview

You are given a simple SQLITE3 database for an attendance system with a small set of data.
An employee can perform one of two actions: CheckIn or CheckOut. When an action is performed, it is stored at 2 levels:

An attendance entry is created for the day if no actions were previously performed on that day
An attendance action entry is created recording the time for this specific action and linked to the day’s attendance record

For example, if EMP01 checks in at midnight of 2020-04-01, two records are created:

A record in the Attendance table with date 2020-04-01 and employee EMP01
A record in the AttendanceActions table referencing the previous attendance record with time 2020-04-01 12:00 AM

The time stamps are stored in local server time, which is in the Cairo timezone (UTC + 2). This is not a good practice, but it’s based on a real-world case.

Requirements

01 Check day attendance for employee

Write a function that accepts an employee code (e.g. EMP01) and a date (e.g. 2020-04-01) and reports whether the employee has attended that day and how long.
Examples

get_attendance('emp01', '2020-04-01')

on the sample data should return the following dictionary:

{
    "attended": True,
    "duration": "12:00"
}

The employee attended from 12:00 AM to 12:00 PM, which is exactly 12 hours.
For 2020-04-02, the employee attended from 12:05 AM to 11:50 AM (11:45 night shift), then checked in again for his next shift at 11:50 PM (so 10 minutes before midnight the next day) for a total of 11:55:

{
    "attended": True,
    "duration": "11:55"
}

For 2020-04-03, the employee checked out at 12:05 PM for a duration of 12:05 (12 hours 5 minutes) calculated from midnight since his last check-in was the previous day.

Solution

from datetime import datetime, timedelta
import sqlite3

def _convert_string_datetime(time):
    return datetime.strptime(time, '%Y-%m-%d %I:%M %p')


def get_attendance(employee: str, tdate: str):
    emp = employee
    start_time = datetime.strptime(tdate, '%Y-%m-%d') - timedelta(minutes=15)
    end_time = (start_time + timedelta(days=1, minutes=15))
    query = '''
    SELECT *, et.ActionTime forwardTime FROM
        (SELECT
            ac.id,
            ac.ActionTime,
            ac.Action,
            ac.Attendanceid,
            a.employee,
            (LEAD  (ac.id, 1,0) OVER (ORDER BY ac.id)) forwardID 
        FROM AttendanceActions ac
        INNER JOIN Attendance a ON ac.Attendanceid = a.id
        WHERE a.employee = '{}' ORDER BY ac.id) ee 
    LEFT JOIN AttendanceActions et ON ee.forwardID = et.id
    '''.format(emp)
    with sqlite3.connect('attendance.db') as con:
        cur = con.cursor()
        db_res = cur.execute(query)
        p_res = db_res.fetchall()
        pt_res = list(map(lambda x: x[1] - x[0], filter(
            lambda x: start_time < x[0] < end_time and x[2] == 'CheckIn', map(
                lambda x: ((_convert_string_datetime(x[1]) if x[1] else 0),
                           (_convert_string_datetime(x[8]) if x[8] else 0), x[2]), p_res))))
        if not pt_res:
            return {
                "attended": False,
                "duration": "00:00"
            }
        else:
            return {
                "attended": True,
                "duration": str(pt_res[0])
            }


print(get_attendance('EMP01', '2020-04-01'))
print(get_attendance('EMP01', '2020-04-02'))
print(get_attendance('EMP01', '2020-04-03'))
print(get_attendance('EMP02', '2020-04-01'))
print(get_attendance('EMP02', '2020-04-02'))
print(get_attendance('EMP02', '2020-04-03'))

Separating Quick Create From Typical Create in Odoo 15

Khaled Said — Mon, 23 Jan 2023 01:17:47 +0000

Odoo supports quick create option to related fields of type Many2one. Such capability let the user create an instant record based on the record name. A case may arise where you may need to differentiate between quick create & the typical create in the backend create method. The easy way to get the backend aware of such create method is through the context parameter. In the context parameter you could pass such information that the call is from the quick create. However you need to the update the BasicModel object of the web client. It is located in the method _applyX2OneChange. As the method full of promises calls. The only way to update it, is to override it. The method definition could be found there:
https://github.com/odoo/odoo/blob/3fdc00f74338fc9eed913771ff28ec447650a0db/addons/web/static/src/legacy/js/views/basic/basic_model.js#L1642
so you could scaffold a new odoo module & create a new javascript file then add it to the manifest definition. your javascript file would be as following. please note line 18 where I updated the context:

1 odoo.define("product_seq.BasicModel", function (require) {
2     "use strict";
3 
4     var BasicModel = require("web.BasicModel");
5 
6     BasicModel.include({
7         _applyX2OneChange: async function (record, fieldName, data, options) {
8             options = options || {};
9             var self = this;
10              if (!data || (!data.id && !data.display_name)) {
11                 record._changes[fieldName] = false;
12                 return Promise.resolve();
13              }
14              const field = record.fields[fieldName];
15              const coModel = field.type === "reference" ? data.model : field.relation;
16              const allowedTypes = ["many2one", "reference"];
17              if (allowedTypes.includes(field.type) && !data.id && data.display_name) {
18                  let _context = this._getContext(record, {
19                      fieldName: fieldName,
20                      viewType: options.viewType,
21                      additionalContext: {'quick_create': true},
22                 }) 
23                 const result = await this._rpc({
24                     model: coModel,
25                     method: "name_create",
26                     args: [data.display_name],
27                     context: _context,
28                });
29                 if (!result) {
30                     record._changes[fieldName] = false;
31                     return Promise.resolve();
32                 }
33                 data = { id: result[0], display_name: result[1] };
34             }
35             var relatedID;
36             if (record._changes && fieldName in record._changes) {
37                 relatedID = record._changes[fieldName];
38             } else {
39                 relatedID = record.data[fieldName];
40             }
41             var relatedRecord = this.localData[relatedID];
42             if (relatedRecord && data.id === this.localData[relatedID].res_id) {
43                 return Promise.resolve();
44             }
45             var rel_data = _.pick(data, "id", "display_name");
46             const viewType = options.viewType || record.viewType;
47             const fieldInfo = record.fieldsInfo[viewType][fieldName] || {};
48             const fieldOptions = fieldInfo.options || {};
49             var def;
50             if (
51                 rel_data.display_name === undefined ||
52                 fieldOptions.always_reload
53             ) {
54                 def = this._rpc({
55                     model: coModel,
56                     method: "name_get",
57                     args: [data.id],
58                     context: this._getContext(record, { fieldName, viewType }),
59                 }).then(function (result) {
60                     rel_data.display_name = result[0][1];
61                 });
62             }
63             return Promise.resolve(def).then(function () {
64                 var rec = self._makeDataPoint({
65                     context: record.context,
66                     data: rel_data,
67                     fields: {},
68                     fieldsInfo: {},
69                     modelName: coModel,
70                     parentID: record.id,
71                 });
72                 record._changes[fieldName] = rec.id;
73             });
74         },
75     });
76 });

Dynamic Selection List for Selection Fields in Odoo ft

Khaled Said — Sat, 05 Feb 2022 05:17:18 +0000

Dynamic list can be approached by 3 patterns when defining selection fields in Odoo. These patterns are old however some colleagues may find them weird. These patterns are helpful instead of hard-coding the definition of each related fields.Selection.

The First Pattern:
You could define a global variable then to import in your class file definition. Such as the following global variable TYPE_TAX_USE:
addons/account/models/account_tax.py

# base class with the first definition
TYPE_TAX_USE = [
    ('sale', 'Sales'),
    ('purchase', 'Purchases'),
    ('none', 'None'),
]


class AccountTax(models.Model):
    _name = 'account.tax'

    type_tax_use = fields.Selection(TYPE_TAX_USE, string='Tax Type', required=True, default='sale')

Now, You could import the variable as following:
addons/account/models/chart_template.py

from odoo.addons.account.models.account_tax import TYPE_TAX_USE


class AccountTaxTemplate(models.Model):
    _name = 'account.tax.template'

    type_tax_use = fields.Selection(TYPE_TAX_USE, string='Tax Type', required=True, default='sale',)

The Second Pattern:
if the model have a relation with your model. You could use the related attribute in the field definition. internal_type field is an example in the following snippet:
addons/account/models/account_account.py

class AccountAccount(models.Model):
    _name = 'account.account'

    user_type_id = fields.Many2one('account.account.type', string='Type', required=True,)
    internal_type = fields.Selection(related='user_type_id.type', string='Internal Type' store=True, readonly=True)

The Third Pattern:
you could use a function to return such list. This function should also be outside the class definition for auto invocation.
odoo/addons/base/models/res_partner.py

import pytz

_tzs = [(tz, tz) for tz in sorted(pytz.all_timezones, key=lambda tz: tz if not tz.startswith('Etc/') else '_')]
def _tz_get(self):
    return _tzs


class Partner(models.Model):
_name = 'res.partner'

    tz = fields.Selection(_tz_get, string='Timezone', default=lambda self: self._context.get('tz'),)

Another example could be as following:

def _get_purchase_order_states(self):
    return self.env['purchase.order']._fields['state'].selection

class PurchaseReport(models.Model):
    _name = 'purchase.report'

    state = fields.Selection(_get_purchase_order_states, string='Order States')

(quick tip) how to get Odoo version from code

Khaled Said — Tue, 30 Mar 2021 01:41:50 +0000

sometimes, you may need to prepare an Odoo development environment. you cloned the repository. however you need to get the Odoo version from code before even running it. in such scenario you could use grep to retrieve the Odoo version, as following:

grep -r -E "^version_info" {{PATH}}
# where path would your Odoo code path. so it could be
grep -r -E "^version_info" /home/user/project/odoo

How to remote debugging Odoo Docker images (Python based framework)

Khaled Said — Sat, 05 Sep 2020 23:10:08 +0000

In order to understand how remote debugging works for all python services or apps which based on it such as Odoo, Flask, Django, Web2py or whatever. you have to understand three different concepts the docker container, the debugger, the python app server (in our case it's Odoo). so in many cases, when running Odoo from docker it is like the following image:

and what you really need to be able to debug would be like the following image:

please note the difference:

without debugging you have two ports, one internal the other is external which will pass http requests from the browser to Odoo & vice versa. however after debugging you have 4 ports, 2 of them for the http request & the other 2 for debugging information (which is based on json in our case) from Vscode to debugpy & vice versa (you could also use 2 ports by the way).
without debugging your entrypoint would be whats defined in the Dockerfile. with debugging, you modify the entrypoint to be debugpy. which will be responsible for running Odoo

so to debug Odoo you will be doing as following:

Edit your docker.dev file & insert RUN pip3 install -U debugpy. this will install a python package debugpy instead of the deprecated one ptvsd because your vscode (local) will be communicating with debugpy (remote) server of your docker image using it.
1. Start your container. you will be starting the python package that you just installed debugpy. it could be as next command from your shell.

docker-compose run --rm -p 8888:8888 -p 8869:8069 {DOCKER IMAGE[:TAG|@DIGEST]} /usr/bin/python3 -m debugpy --listen 0.0.0.0:8888 /usr/bin/odoo --db_user=odoo --db_host=db --db_password=odoo

Prepare your launcher file as following. please note that port will be related to odoo server. debugServer will be the port for the debug server

{
    "name": "Odoo: Attach",
    "type": "python",
    "request": "attach",
    "port": 8869,
    "debugServer": 8888,
    "host": "localhost",
    "pathMappings": [
        {
            "localRoot": "${workspaceFolder}",
            "remoteRoot": "/mnt/extra-addons",
        }
    ],
    "logToFile": true
}