How to aggregate column with non-aggregable value in an Array using Group in PostgreSQL

Let’s imagine, scenario is like the following:

We have three columns with the following values in a table named ‘sale_line’

----------------------------------------
product_id	qty_done	sale_id
----------------------------------------
1010		5.0		101
1010		1.0		102
1010		2.0		103
1012		1.0		104
1012		4.0		105
1012		4.0		106
1012		9.0		107

We have a product_id column that contains which product we are referring to, a qty_done column, which contains the number of products sold, and sale_id refers to the sale order where the product_id was dispatched. Now the example table contains how many of each product dispatched in different sale orders. We can write a query, to group the product_id and the total number of products sold using aggregation

select product_id, sum(qty_done) as total_sold from sale_line group by product_id;

The producing result would be the following:

---------------------------
product_id	total_sold
---------------------------
1010		8.0		
1012		17.0	

We can see, how group by can give you the benefit of aggregation. But have you noticed, we have a column called ‘sale_id’ that doesn’t go with any aggregation function like ‘count’, ‘sum’ or ‘avg’. Can we use them for any purpose?

Sometimes, you may be interested to track down the orders responsible for a set of products using group by when each products will contain the list of orders responsible for causing the total sell. Can we accomplish that in one query?

We actually can. We can concat distinct ids and put them in a resulting array instead of our known aggregation function like ‘count’ or ‘sum’. Postgres provides a array aggregation function called ‘array_agg’, it can be used to produce such result. Have a look at the following query:

select product_id, sum(qty_done) as total_sold, array_agg(sale_id) as sale_ids from sale_line group by product_id;

The producing result would be the following:

-----------------------------------------
product_id	total_sold	sale_ids
-----------------------------------------
1010		8.0		[101, 102, 103]
1012		17.0		[104, 105, 106, 107]

As you can see, we have made postgres return us an array that contains the sale_ids for consisting total_sold even though, sale_id column wasn’t conventionally aggregable or let’s say different than the usual numeric pattern.

This technique can have many use cases. For example if you have a different model/table for managing sale orders, purchase orders, pickings and invoices while, your sale orders contains the price information, and the pickings doesn’t, but picking contains the actual number of products got dispatched, then you may first aggregate the total_sold from the picking table, then produce an average price using the sale_ids you have produced from the picking table using group by and array aggregation, then merge them. This seems quite complicated, I understand, but again, it’s all about putting a break to your reporting time, and manage scalability of your application, putting a constant cost based reporting algorithm using single sql query, even if you process 10 times more orders in future.

How to get purchase_id from stock_picking table in Odoo?

stock_picking is one of the core model for Odoo if you using Odoo for ‘Inventory’. Now, if you use ‘Sales’ module, then stock_picking would be extended with a new field for model and column for database table, naming ‘sale_id’. This can be used to detect if the picking is originated from a sale order or not. But if you install ‘Purchase’ module, then stock_picking model is extended with ‘purchase_id’ like the ‘sale_id’ for purchases, but the database isn’t expanded with a column like ‘Sale’ module.

What does this mean?

This means, if you use Odoo ORM, only then, you may use purchase_id of a stock_picking. An example could be like the following. Let’s say, we would like to pick the pickings that originated from purchase orders, aka, GRN, we could use something like this:

purchase_pickings = self.env['stock.picking'].search([('purchase_id', '!=', False)])

This works, only if you are not trying to make a report from a huge lot of pickings, purchase orders and sale orders, when you want to use SQL statement to produce efficient joins and generate the report quickly.

Let me demonstrate what I meant

We know, stock_picking has a field called sale_id and also this also belongs to the database column as well. Hence, to get all the pickings belongs to sale order, we may first use the ORM:

sale_pickings = self.env['stock.picking'].search([('sale_id', '!=', False)])

or a direct PostGRE SQL

query = """select * from stock_picking where sale_id is not null"""
self.env.cr.execute(query)
result = self.env.cr.fetchall()

Now, the second example is not only faster, but also, it allows you to extend the facility further to use joins or select specific field of a table result, which is only possible using ‘read’ Odoo ORM method, again, domain specification is not permissible like it is available in ‘search’.

We are able to do things like the following with the sql:

query = """select sale_order.name, stock_picking.name from stock_picking left join on stock_picking.sale_id = sale_order.id where stock_picking.sale_id is not null"""
self.env.cr.execute(query)
result = self.env.cr.fetchall()

This would give you a result of each sale order with it’s picking name. To produce a result like the above using ORM is costly as it follows ‘N+1’ algorithm, hence inefficient in making reports or scaling the software.

Now, we understand, we are able to use such field and make the reports efficient using SQL as sale_id is distinctively available in the database. But what if you want to check how the product has been purchased, and then sold? Then, we also need purchase_order model to connect to our above query, right? But unfortunately, as ‘Purchase’ module doesn’t add a column purchase_id, we are unable to use this directly.

So, how can we still use purchase_id in the SQL Query to generate report in Odoo?

First, we need to see, how purchase_id is added in Odoo.

purchase_id is added in stock_picking model in the ‘purchase_stock’ module. If you open the following file:

purchase_stock/models/stock.py

you may see, how purchase_id is defined as related Many2one field:

class StockPicking(models.Model):
    _inherit = 'stock.picking'

    purchase_id = fields.Many2one('purchase.order', related='move_lines.purchase_line_id.order_id',
        string="Purchase Orders", readonly=True)

A related field in Odoo, is like a pointer, a syntactic sugar of foreign key for less used fields. If the field is highly used, this might cause performance issue, as Odoo has to do multiple lookups unlike direct lookup for a related field. Now, get to the point, purchase_id is related to ‘move_lines.purchase_line_id.order_id’. This is a long relation. Let me go one by one:

  1. move_lines : stock_picking has an One2many relation with stock.move model, that derives the available moves for the picking.
  2. purchase_line_id: Each move line derived from a purchase order line, and while doing so, it keeps the ID of the purchase order line in a foreign key of stock.move model, namely purchase_line_id.
  3. order_id: Each purchase_order_line has a foreign key with the purchase.order model kept in order_id field.

Now, we know, how the purchase_id derives the purchase_order id using the following relation:

Picking > Moves > Purchase Order Line > Purchase Order

Now we can use the following kind of relation for detecting purchase order from stock picking:

select purchase_order.name, stock_picking.name from stock_picking left join stock_move on stock_move.picking_id = stock_picking.id left join purchase_order_line on purchase_order_line.id = stock_move.purchase_line_id left join purchase_order on purchase_order.id = purchase_order_line.order_id where stock_move.purchase_line_id is not null group by stock_picking.name, purchase_order.name

Here, we are able to get the picking and purchase in relation with one query. This concept can be used to derive many data, like, let’s say, you would like to see, how many of your products are purchased, then, sold and returned, all can be done in few queries, without having N+1 problem.

How to Fix Locale Font Issue with Odoo Qweb Reports

Issue

When looking at the html report in Odoo, locale fonts look ok, but if you download the Qweb report to print in pdf format, it prints gibberish. How to fix that?

Resolution

Odoo uses a templating engine for reporting called ‘Qweb’. Qweb can be used to generate two types of reports. One is HTML and the other is PDF. Odoo primarily uses Qweb engine to generate the HTML code. After that, it uses a tool called ‘wkhtmltopdf’ to convert the report to pdf and make it printable. Now when, we look at the HTML version of the report, fonts are shown based on Unicode supported browsers or the fonts you have installed on your computer. But when you try to convert this to PDF using wkhtmltopdf, that tool has to have exclusive access to those fonts to be able to convert them from HTML to pdf for you. As wkhtmlpdf command runs in the server you have installed Odoo, hence, you would need to install the font package in the server.

In my case, I required to install Bengali fonts. For CentOS, it is available under the lohit package, that contains several indian fonts including bengali. To install bengali font package in CentOS 7, use the following command:

yum install lohit-bengali* -y

Once done, your wkhtmltopdf should be able to read the bengali fonts from your html/qweb templates and able to convert them to PDF for you.

How to Add fields to res.users or res.partners model in Odoo

res.users and res.partners tables are two base tables of odoo. If you would like to inherit and extend them, remember, you can’t do it from the Odoo user view. The reason is, when you do an upgrade from the user view, it has to be something that works over base module, not base module itself. Hence, you will get a 500 error or internal server error when trying to upgrade the module.

We will make a simple module for res.users to extend the model to add a field called ‘access_token’ for each user, and generate a key automatically when a user is added.

I will only post the model file and the view file here. I expect you already know how to write an Odoo module.

This is my res_users.py file

from odoo import api, fields, models
import string, random

class res_users_ex(models.Model):
    _inherit = 'res.users'

    access_token = fields.Text(string='API Access Token', default=False)

    def create(self, vals):
        key = ''.join(random.choices(string.ascii_lowercase + string.digits, k = 48))
        user_id = super(res_users_ex, self).create(vals)
        if key:
            user_id.access_token = key

        return user_id

Here is the xml view file:

<?xml version="1.0" encoding="UTF-8"?>
<odoo>
    <record model="ir.ui.view" id="view_res_users_access_token">
        <field name="name">res.users.add.access.token</field>
        <field name="model">res.users</field>
        <field name="inherit_id" ref="base.view_users_form"/>
        <field name="type">form</field>
        <field name="arch" type="xml">
        <xpath expr="//notebook" position="inside">
            <page string="API Details">
               <group col="4">
                  <field name="access_token"/>
                </group>
             </page>
        </xpath>
        </field>
    </record>
</odoo>

Now, after you have added this to a module, you can not simply upgrade this from App >> Module >> Upgrade. You need to upgrade this module via command line like the following:

First switch to your odoo user, in my case, it is ‘odoo’

su - odoo

Now, first stop your current odoo with the following:

service odoo14 stop

Once done, now you can upgrade the module with the following:

/usr/bin/scl enable rh-python36 -- /opt/odoo/odoo14-venv/bin/python3 /opt/odoo/odoo14/odoo-bin -d my_database-u res_users_access_token

The command above is explained like the following:

python3 odoo-bin -d your_databasename -u module_name

In my case, I use virtual environment and scl for python, hence the python3 source is like the following:

/usr/bin/scl enable rh-python36 -- /opt/odoo/odoo14-venv/bin/python3

The next one is the binary of odoo with it’s location, which should be odoo-bin. With the parameter -d, you give your database name, and with the parameter -u, you need to give your module name. After you run the command, you should see no ‘Error’ or Red marked line in your console. If not, it shall be upgraded. Now do control + c, and start your odoo again to see the new fields being visible in your Users tab.

Odoo Error – virtual real time limit (151/120s) reached.

There are times, when you might suddenly see your Odoo is shutdown automatically, without warning. Once you enable to logging, you could see an error like the following:

virtual real time limit (151/120s) reached.

or in full details like the following

2021-04-22 06:46:44,054 32685 WARNING ? odoo.service.server: Thread <Thread(odoo.service.http.request.140015617943296, started 140015617943296)> virtual real time limit (151/120s) reached.
2021-04-22 06:46:44,054 32685 INFO ? odoo.service.server: Dumping stacktrace of limit exceeding threads before reloading
2021-04-22 06:46:44,060 32685 INFO ? odoo.tools.misc:
# Thread: <Thread(odoo.service.http.request.140015617943296, started 140015617943296)> (db:n/a) (uid:n/a) (url:n/a)
File: "/opt/rh/rh-python36/root/usr/lib64/python3.6/threading.py", line 884, in _bootstrap
  self._bootstrap_inner()
File: "/opt/rh/rh-python36/root/usr/lib64/python3.6/threading.py", line 916, in _bootstrap_inner
  self.run()
File: "/opt/rh/rh-python36/root/usr/lib64/python3.6/threading.py", line 864, in run
  self._target(*self._args, **self._kwargs)
File: "/opt/rh/rh-python36/root/usr/lib64/python3.6/socketserver.py", line 654, in process_request_thread
  self.finish_request(request, client_address)
File: "/opt/rh/rh-python36/root/usr/lib64/python3.6/socketserver.py", line 364, in finish_request
  self.RequestHandlerClass(request, client_address, self)
File: "/opt/rh/rh-python36/root/usr/lib64/python3.6/socketserver.py", line 724, in __init__
  self.handle()
File: "/opt/odoo/odoo14-venv/lib64/python3.6/site-packages/werkzeug/serving.py", line 329, in handle
  rv = BaseHTTPRequestHandler.handle(self)
File: "/opt/rh/rh-python36/root/usr/lib64/python3.6/http/server.py", line 418, in handle
  self.handle_one_request()
File: "/opt/odoo/odoo14-venv/lib64/python3.6/site-packages/werkzeug/serving.py", line 360, in handle_one_request
  self.raw_requestline = self.rfile.readline()
File: "/opt/rh/rh-python36/root/usr/lib64/python3.6/socket.py", line 586, in readinto
  return self._sock.recv_into(b)
2021-04-22 06:46:44,060 32685 INFO ? odoo.service.server: Initiating server reload

This is because Odoo is killing zombie processes and probably mistakenly crashing your Odoo completely while doing so. The parameter that is used for this purpose, can be found in Odoo documentation:

https://www.odoo.com/documentation/14.0/reference/cmdline.html

--limit-time-real <limit>
Prevents the worker from taking longer than <limit> seconds to process a request. If the limit is exceeded, the worker is killed.

Differs from --limit-time-cpu in that this is a “wall time” limit including e.g. SQL queries.

Defaults to 120.

You may start your service command with something like –limit-time-real 100000 to avoid Odoo from auto killing processes. A command could look like the following if you edit your service file located at:

/etc/systemd/system/odoo14.service

The exec would be like the following:

ExecStart=/usr/bin/scl enable rh-python36 -- /opt/odoo/odoo14-venv/bin/python3 /opt/odoo/odoo14/odoo-bin -c /etc/odoo.conf --limit-time-real=100000

Once the change is done, save the file, and reload the system daemon and restart your Odoo

systemctl daemon-reload
service odoo14 restart

How to Setup Odoo 14 in CentOS 7

Odoo is currently one of the most popular tool for business purposes. It has a community edition, that allows managing ERP at very low cost. Odoo was previously known as OpenERP. Odoo requires to be installed on a dedicated server or VPS. Odoo 14 had come out already. I will have a straight forward how to on installing the latest Odoo 14 in CentOS 7.

Log in to your system and update

First step would be to login to your system and then update the system using yum.

ssh [email protected]_ip

You may check the CentOS version from the redhat release file using the following:

cat /etc/redhat-release

It should show you something like the following if you

CentOS Linux release 7.8.2003 (Core)

Now, you may try updating the system with yum

yum update -y

Once done, now install the EPEL repository as we need it to satisfy a couple of dependecies:

yum install epel-release

Install Python 3.6 packages and Odoo dependencies

We need Python 3.6 at least to run Odoo 14. Odoo 13 also ran on Python 3.6. We will use ‘Software Collection (scl)’ repository to install and use Python 3.6. To find the available Python versions in SCL, you may check the following:

SCL Repository for Python

Now, to install Python 3.6 using SCL, we first need to install the SCL repository for Centos:

yum install centos-release-scl

Once the SCL is loaded, now, you may install the python 3.6 using the following command:

yum install rh-python36

Once the Python is installed, now we will install several tools and packages for Odoo dependencies with the following command:

yum install git gcc nano wget nodejs-less libxslt-devel bzip2-devel openldap-devel libjpeg-devel freetype-devel

Create Odoo User

We now need to create a system user and group for Odoo and define a home directory to /opt/odoo

useradd -m -U -r -d  /opt/odoo -s /bin/bash odoo

You may use any username here, but remember to create the same username for the PostgreSQL as well.

Install PostgreSQL

CentOS base repository unfortunately, comes with Postgresql 9.2. But we want to use PostgreSQL 10 for our Odoo installation. You may check the available PostgreSQL for CentOS 7 using the following command:

yum list postgresql*

As by default CentOS 7 does not provide the PostgreSQL 10, we would use PostgreSQL official repository to download and install the 10 version.

First, we install the Postgres Yum Repository using the following command:

yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Now, you may install PostgreSQL 10 and related required packages using the following command:

yum install postgresql10 postgresql10-server postgresql10-contrib postgresql10-libs -y

Now, we need to initialize the postgres database and start it. You may do that using the following:

# Initialize the DB
/usr/pgsql-10/bin/postgresql-10-setup initdb

# Start the database
systemctl start postgresql-10.service

If everything goes alright, now you may check the postgresql 10 status:

[[email protected] bin]# systemctl status postgresql-10.service
● postgresql-10.service - PostgreSQL 10 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-10.service; disabled; vendor preset: disabled)
   Active: active (running) since Mon 2021-04-05 16:42:15 EDT; 5s ago
     Docs: https://www.postgresql.org/docs/10/static/
  Process: 6380 ExecStartPre=/usr/pgsql-10/bin/postgresql-10-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 6386 (postmaster)
    Tasks: 8
   Memory: 13.5M
   CGroup: /system.slice/postgresql-10.service
           ├─6386 /usr/pgsql-10/bin/postmaster -D /var/lib/pgsql/10/data/
           ├─6388 postgres: logger process
           ├─6390 postgres: checkpointer process
           ├─6391 postgres: writer process
           ├─6392 postgres: wal writer process
           ├─6393 postgres: autovacuum launcher process
           ├─6394 postgres: stats collector process
           └─6395 postgres: bgworker: logical replication launcher

Now you may enable Postgres to start when booting up using the systemctl enable command:

systemctl enable postgresql-10.service

Now, we need to create a database user for our Odoo installation. You may do that using the following:

su - postgres -c "createuser -s odoo"

Note: If you have created a different user for Odoo installation other than ‘odoo’ than you should change the username here as well.

Install Wkhtmltopdf

Wkhtmltopdf is a open source tool to make html in pdf format so that you may print pdf reports. This tool is used by Odoo and requires to be installed as dependency. CentOS 7 repository does not provide the latest version of this tool, and Odoo requires you to use the latest version. Hence, we require to download the latest version from the Wkhtmltopdf website and install it. To do that, you may first visit the page:

https://wkhtmltopdf.org/downloads.html

The page gives you the direct rpm download link for each version of CentOS/Ubuntu/Mac etc. Download the stable version for CentOS 7. At the time of writing, the URL for CentOS 7 x86_64 bit is the following:

https://github.com/wkhtmltopdf/packaging/releases/download/0.12.6-1/wkhtmltox-0.12.6-1.centos7.x86_64.rpm

You may install this using the following:

cd /opt/
wget https://github.com/wkhtmltopdf/packaging/releases/download/0.12.6-1/wkhtmltox-0.12.6-1.centos7.x86_64.rpm
yum localinstall wkhtmltox-0.12.6-1.centos7.x86_64.rpm

Install and Configure Odoo 14

If you have come all through here, that means you are done with the all dependency installations before starting to download Odoo 14 source code. We will download Odoo 14 from it’s Github repo and use virtualenv to create an isolated python environment to install this python software.

First, login as odoo from root:

su - odoo

Clone the Odoo source code from Github repository:

git clone https://www.github.com/odoo/odoo --depth 1 --branch 14.0 /opt/odoo/odoo14

This will bring the Odoo 14 branch from the Odoo repository and put it inside the folder /opt/odoo/odoo14

Now, we need to enable software collections in order to access python binaries:

scl enable rh-python36 bash

Then we need to create a virtual environment to complete the installation:

cd /opt/odoo
python3 -m venv odoo14-venv

Now, you may activate the virtual environment you have just created:

source odoo14-venv/bin/activate

Now, we upgrade the pip and install the wheel library:

pip install --upgrade pip
pip3 install wheel

Once done, now we can using pip3 to install all the required Python modules from the requirements.txt file:

pip3 install -r odoo14/requirements.txt

Once the installation is complete, now we can deactivate the virtual environment and get back to the root user

deactivate && exit ; exit

If you think, you will create custom modules, you may now create it and give odoo the permission accordingly:

mkdir /opt/odoo/odoo14-custom-addons
chown odoo: /opt/odoo/odoo14-custom-addons

Now, we can fill up the odoo configuration file. First open the odoo.conf file:

nano /etc/odoo.conf

You may paste the following inside:

[options]
; This is the password that allows database operations:
admin_passwd = set_the_password_to_create_odoo_database
db_host = False
db_port = False
db_user = odoo
db_password = False
addons_path = /opt/odoo/odoo14/addons,/opt/odoo/odoo14-custom-addons
; You can enable log file with uncommenting the next line
; logfile = /var/log/odoo14/odoo.log

Please do not forget to change the password ‘set_the_password_to_create_odoo_database’ with a new strong password. This would be used to create Odoo databases from the login screen.

Create the systemd service file and start Odoo 14

Now, we will create a service file, to be able to start, stop and restart Odoo daemon. To do that, first create a service file using the following:

nano /etc/systemd/system/odoo14.service
[Unit]
Description=Odoo14
Requires=postgresql-10.service
After=network.target postgresql-10.service
[Service]
Type=simple
SyslogIdentifier=odoo14
PermissionsStartOnly=true
User=odoo
Group=odoo
ExecStart=/usr/bin/scl enable rh-python36 -- /opt/odoo/odoo14-venv/bin/python3 /opt/odoo/odoo14/odoo-bin -c /etc/odoo.conf
StandardOutput=journal+console
[Install]
WantedBy=multi-user.target

Now, save the file and exit.

Now, you need to reload the systemd daemon to be able to read the latest changes you have made to services. To do that, run:

systemctl daemon-reload

Finally, now we can start Odoo 14 instance using the following command:

systemctl start odoo14

If you are interested to check the status of the instance, you may do this:

systemctl status odoo14

It show green active running, if everything worked out. If you see no error, you may now enable Odoo to start during the boot:

systemctl enable odoo14

If you would like to see the logs, you may either use the journal tools like the following:

journalctl -u odoo14

or uncomment the following line to log the debugs in /etc/odoo.conf

logfile = /var/log/odoo14/odoo.log

After making any change to /etc/odoo.conf, do not forget the restart the Odoo14 instance using systemctl.

Test the Installation


Odoo is currently one of the most popular tool for business purposes. It has a community edition, that allows managing ERP at very low cost. Odoo was previously known as OpenERP. Odoo requires to be installed on a dedicated server or VPS. Odoo 13 had come out on October, 2019. Odoo 14 hasn’t been released yet for production. I will have a straight forward how to on installing the latest Odoo 13 in CentOS 7.

Log in to your system and update

First step would be to login to your system and then update the system using yum.ssh [email protected]_ip

You may check the CentOS version from the redhat release file using the following:cat /etc/redhat-release

It should show you something like the following if youCentOS Linux release 7.8.2003 (Core)

Now, you may try updating the system with yumyum update -y

Once done, now install the EPEL repository as we need it to satisfy a couple of dependecies:yum install epel-release

Install Python 3.6 packages and Odoo dependencies

We need Python 3.6 at least to run Odoo 13. Odoo 12 had support for Python 3.5, unfortunately, Odoo 13 doesn’t. We will use ‘Software Collection (scl)’ repository to install and use Python 3.6. To find the available Python versions in SCL, you may check the following:

SCL Repository for Python

Now, to install Python 3.6 using SCL, we first need to install the SCL repository for Centos:yum install centos-release-scl

Once the SCL is loaded, now, you may install the python 3.6 using the following command:yum install rh-python36

Once the Python is installed, now we will install several tools and packages for Odoo dependencies with the following command:yum install git gcc nano wget nodejs-less libxslt-devel bzip2-devel openldap-devel libjpeg-devel freetype-devel

Create Odoo User

We now need to create a system user and group for Odoo and define a home directory to /opt/odoouseradd -m -U -r -d /opt/odoo -s /bin/bash odoo

You may use any username here, but remember to create the same username for the PostgreSQL as well.

Install PostgreSQL

CentOS base repository unfortunately, comes with Postgresql 9.2. But we want to use PostgreSQL 9.6 for our Odoo installation. You may check the available PostgreSQL for CentOS 7 using the following command:yum list postgresql*

As by default CentOS 7 does not provide the PostgreSQL 9.6, we would use PostgreSQL official repository to download and install the 9.6 version.

First, we install the Postgres Yum Repository using the following command:yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Now, you may install PostgreSQL 9.6 and related required packages using the following command:yum install postgresql96 postgresql96-server postgresql96-contrib postgresql96-libs

Now, we need to initialize the postgres database and start it. You may do that using the following:# Initialize the DB/usr/pgsql-9.6/bin/postgresql96-setup initdb# Start the databasesystemctl start postgresql-9.6.service

Now you may enable Postgres to start when booting up using the systemctl enable command:systemctl enable postgresql-9.6.service

Now, we need to create a database user for our Odoo installation. You may do that using the following:su – postgres -c “createuser -s odoo”

Note: If you have created a different user for Odoo installation other than ‘odoo’ than you should change the username here as well.

Install Wkhtmltopdf

Wkhtmltopdf is a open source tool to make html in pdf format so that you may print pdf reports. This tool is used by Odoo and requires to be installed as dependency. CentOS 7 repository does not provide the latest version of this tool, and Odoo requires you to use the latest version. Hence, we require to download the latest version from the Wkhtmltopdf website and install it. To do that, you may first visit the page:https://wkhtmltopdf.org/downloads.html

The page gives you the direct rpm download link for each version of CentOS/Ubuntu/Mac etc. Download the stable version for CentOS 7. At the time of writing, the URL for CentOS 7 x86_64 bit is the following:https://github.com/wkhtmltopdf/packaging/releases/download/0.12.6-1/wkhtmltox-0.12.6-1.centos7.x86_64.rpm

You may install this using the following:cd /opt/wget https://github.com/wkhtmltopdf/packaging/releases/download/0.12.6-1/wkhtmltox-0.12.6-1.centos7.x86_64.rpmyum localinstall wkhtmltox-0.12.6-1.centos7.x86_64.rpm

Install and Configure Odoo 13

If you have come all through here, that means you are done with the all dependency installations before starting to download Odoo 13 source code. We will download Odoo 13 from it’s Github repo and use virtualenv to create an isolated python environment to install this python software.

First, login as odoo from root:su – odoo

Clone the Odoo source code from Github repository:git clone https://www.github.com/odoo/odoo –depth 1 –branch 13.0 /opt/odoo/odoo13

This will bring the Odoo 13 branch from the Odoo repository and put it inside the folder /opt/odoo/odoo13

Now, we need to enable software collections in order to access python binaries:scl enable rh-python36 bash

Then we need to create a virtual environment to complete the installation:cd /opt/odoopython3 -m venv odoo13-venv

Now, you may activate the virtual environment you have just created:source odoo13-venv/bin/activate

Now, we upgrade the pip and install the wheel library:pip install –upgrade pippip3 install wheel

Once done, now we can using pip3 to install all the required Python modules from the requirements.txt file:pip3 install -r odoo13/requirements.txt

Once the installation is complete, now we can deactivate the virtual environment and get back to the root userdeactivate && exit ; exit

If you think, you will create custom modules, you may now create it and give odoo the permission accordingly:mkdir /opt/odoo/odoo13-custom-addonschown odoo: /opt/odoo/odoo13-custom-addons

Now, we can fill up the odoo configuration file. First open the odoo.conf file:nano /etc/odoo.conf

You may paste the following inside:[options]; This is the password that allows database operations:admin_passwd = set_the_password_to_create_odoo_databasedb_host = Falsedb_port = Falsedb_user = odoodb_password = Falseaddons_path = /opt/odoo/odoo13/addons,/opt/odoo/odoo13-custom-addons; You can enable log file with uncommenting the next line; logfile = /var/log/odoo13/odoo.log

Please do not forget to change the password ‘set_the_password_to_create_odoo_database’ with a new strong password. This would be used to create Odoo databases from the login screen.

Create the systemd service file and start Odoo 13

Now, we will create a service file, to be able to start, stop and restart Odoo daemon. To do that, first create a service file using the following:nano /etc/systemd/system/odoo13.service

and paste the following:[Unit]Description=Odoo13Requires=postgresql-9.6.serviceAfter=network.target postgresql-9.6.service[Service]Type=simpleSyslogIdentifier=odoo13PermissionsStartOnly=trueUser=odooGroup=odooExecStart=/usr/bin/scl enable rh-python35 — /opt/odoo/odoo13-venv/bin/python3 /opt/odoo/odoo13/odoo-bin -c /etc/odoo.confStandardOutput=journal+console[Install]WantedBy=multi-user.target

Now, save the file and exit.

Now, you need to reload the systemd daemon to be able to read the latest changes you have made to services. To do that, run:systemctl daemon-reload

Finally, now we can start Odoo 13 instance using the following command:systemctl start odoo13

If you are interested to check the status of the instance, you may do this:systemctl status odoo13[[email protected] ~]# systemctl status odoo13● odoo13.service – Odoo13 Loaded: loaded (/etc/systemd/system/odoo13.service; enabled; vendor preset: disabled) Active: active (running) since Sun 2020-09-13 08:26:46 EDT; 23h ago Main PID: 24502 (scl) CGroup: /system.slice/odoo13.service ├─24502 /usr/bin/scl enable rh-python36 — /opt/odoo/odoo13-venv/bin/python3 /opt/odoo/odoo13/odoo-bin -c /etc/odoo.conf ├─24503 /bin/bash /var/tmp/sclSWH04z └─24507 /opt/odoo/odoo13-venv/bin/python3 /opt/odoo/odoo13/odoo-bin -c /etc/odoo.conf

It show green active running, if everything worked out. If you see no error, you may now enable Odoo to start during the boot:systemctl enable odoo13

If you would like to see the logs, you may either use the journal tools like the following:journalctl -u odoo13

or uncomment the following line to log the debugs in /etc/odoo.conflogfile = /var/log/odoo13/odoo.log

After making any change to /etc/odoo.conf, do not forget the restart the Odoo13 instance using systemctl.

Test the Installation

You may now test the installation using http://your_server_ip:8069. If everything worked, it should come up. If it doesn’t, you may try stopping your ‘firewalld’ to see if firewall is blocking the port or not:

systemctl stop firewalld

At Mellowhost, we provide Odoo installation and configuration assistance for absolute free of charge. If you are willing to try out any of our VPS for Odoo, you may do so and talk with us through the Live chat or the ticket for Odoo assistance.

Furthermore, Good luck.

How to Enable Logging in Odoo

There are two ways to see the Odoo Logs. One is rough and can be used to see the latest Odoo logs, it’s the Journal tools. You may do this using the following if your Odoo service is installed as odoo13 for example

journalctl -u odoo13

Note: If you are having trouble primarily in installing Odoo properly, you may check the following:

The other way, is the enable logging to a file. This has to be enabled from the odoo.conf file which is located under /etc/ folder. First we open the /etc/odoo.conf file:

nano /etc/odoo.conf

Now, search to see if you have a directive called ‘logfile’. If you don’t, you may add the following to /etc/odoo.conf:

logfile = /var/log/odoo13/odoo.log

If you already have the directive, but commented out, like this:

; logfile = /var/log/odoo13/odoo.log

You may remove the ‘;’ in front of the logfile directive and save the file. Now you may restart your Odoo instance to allow odoo log the information to the file /var/log/odoo13/odoo.log

systemctl restart odoo13

If the restart showing some errors, probably because it is failing to put permission to odoo13 folder. You may try the following:

mkdir /var/log/odoo13
chown -Rf odoo:odoo /var/log/odoo13
systemctl restart odoo13

How to Install Odoo 13 in CentOS 7

Odoo is currently one of the most popular tool for business purposes. It has a community edition, that allows managing ERP at very low cost. Odoo was previously known as OpenERP. Odoo requires to be installed on a dedicated server or VPS. Odoo 13 had come out on October, 2019. Odoo 14 hasn’t been released yet for production. I will have a straight forward how to on installing the latest Odoo 13 in CentOS 7.

Log in to your system and update

First step would be to login to your system and then update the system using yum.

ssh [email protected]_ip

You may check the CentOS version from the redhat release file using the following:

cat /etc/redhat-release

It should show you something like the following if you

CentOS Linux release 7.8.2003 (Core)

Now, you may try updating the system with yum

yum update -y

Once done, now install the EPEL repository as we need it to satisfy a couple of dependecies:

yum install epel-release

Install Python 3.6 packages and Odoo dependencies

We need Python 3.6 at least to run Odoo 13. Odoo 12 had support for Python 3.5, unfortunately, Odoo 13 doesn’t. We will use ‘Software Collection (scl)’ repository to install and use Python 3.6. To find the available Python versions in SCL, you may check the following:

SCL Repository for Python

Now, to install Python 3.6 using SCL, we first need to install the SCL repository for Centos:

yum install centos-release-scl

Once the SCL is loaded, now, you may install the python 3.6 using the following command:

yum install rh-python36

Once the Python is installed, now we will install several tools and packages for Odoo dependencies with the following command:

yum install git gcc nano wget nodejs-less libxslt-devel bzip2-devel openldap-devel libjpeg-devel freetype-devel

Create Odoo User

We now need to create a system user and group for Odoo and define a home directory to /opt/odoo

useradd -m -U -r -d  /opt/odoo -s /bin/bash odoo

You may use any username here, but remember to create the same username for the PostgreSQL as well.

Install PostgreSQL

CentOS base repository unfortunately, comes with Postgresql 9.2. But we want to use PostgreSQL 9.6 for our Odoo installation. You may check the available PostgreSQL for CentOS 7 using the following command:

yum list postgresql*

As by default CentOS 7 does not provide the PostgreSQL 9.6, we would use PostgreSQL official repository to download and install the 9.6 version.

First, we install the Postgres Yum Repository using the following command:

yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Now, you may install PostgreSQL 9.6 and related required packages using the following command:

yum install postgresql96 postgresql96-server postgresql96-contrib postgresql96-libs

Now, we need to initialize the postgres database and start it. You may do that using the following:

# Initialize the DB
/usr/pgsql-9.6/bin/postgresql96-setup initdb

# Start the database
systemctl start postgresql-9.6.service

Now you may enable Postgres to start when booting up using the systemctl enable command:

systemctl enable postgresql-9.6.service

Now, we need to create a database user for our Odoo installation. You may do that using the following:

su - postgres -c "createuser -s odoo"

Note: If you have created a different user for Odoo installation other than ‘odoo’ than you should change the username here as well.

Install Wkhtmltopdf

Wkhtmltopdf is a open source tool to make html in pdf format so that you may print pdf reports. This tool is used by Odoo and requires to be installed as dependency. CentOS 7 repository does not provide the latest version of this tool, and Odoo requires you to use the latest version. Hence, we require to download the latest version from the Wkhtmltopdf website and install it. To do that, you may first visit the page:

https://wkhtmltopdf.org/downloads.html

The page gives you the direct rpm download link for each version of CentOS/Ubuntu/Mac etc. Download the stable version for CentOS 7. At the time of writing, the URL for CentOS 7 x86_64 bit is the following:

https://github.com/wkhtmltopdf/packaging/releases/download/0.12.6-1/wkhtmltox-0.12.6-1.centos7.x86_64.rpm

You may install this using the following:

cd /opt/
wget https://github.com/wkhtmltopdf/packaging/releases/download/0.12.6-1/wkhtmltox-0.12.6-1.centos7.x86_64.rpm
yum localinstall wkhtmltox-0.12.6-1.centos7.x86_64.rpm

Install and Configure Odoo 13

If you have come all through here, that means you are done with the all dependency installations before starting to download Odoo 13 source code. We will download Odoo 13 from it’s Github repo and use virtualenv to create an isolated python environment to install this python software.

First, login as odoo from root:

su - odoo

Clone the Odoo source code from Github repository:

git clone https://www.github.com/odoo/odoo --depth 1 --branch 13.0 /opt/odoo/odoo13

This will bring the Odoo 13 branch from the Odoo repository and put it inside the folder /opt/odoo/odoo13

Now, we need to enable software collections in order to access python binaries:

scl enable rh-python36 bash

Then we need to create a virtual environment to complete the installation:

cd /opt/odoo
python3 -m venv odoo13-venv

Now, you may activate the virtual environment you have just created:

source odoo13-venv/bin/activate

Now, we upgrade the pip and install the wheel library:

pip install --upgrade pip
pip3 install wheel

Once done, now we can using pip3 to install all the required Python modules from the requirements.txt file:

pip3 install -r odoo13/requirements.txt

Once the installation is complete, now we can deactivate the virtual environment and get back to the root user

deactivate && exit ; exit

If you think, you will create custom modules, you may now create it and give odoo the permission accordingly:

mkdir /opt/odoo/odoo13-custom-addons
chown odoo: /opt/odoo/odoo13-custom-addons

Now, we can fill up the odoo configuration file. First open the odoo.conf file:

nano /etc/odoo.conf

You may paste the following inside:

[options]
; This is the password that allows database operations:
admin_passwd = set_the_password_to_create_odoo_database
db_host = False
db_port = False
db_user = odoo
db_password = False
addons_path = /opt/odoo/odoo13/addons,/opt/odoo/odoo13-custom-addons
; You can enable log file with uncommenting the next line
; logfile = /var/log/odoo13/odoo.log

Please do not forget to change the password ‘set_the_password_to_create_odoo_database’ with a new strong password. This would be used to create Odoo databases from the login screen.

Create the systemd service file and start Odoo 13

Now, we will create a service file, to be able to start, stop and restart Odoo daemon. To do that, first create a service file using the following:

nano /etc/systemd/system/odoo13.service

and paste the following:

[Unit]
Description=Odoo13
Requires=postgresql-9.6.service
After=network.target postgresql-9.6.service

[Service]
Type=simple
SyslogIdentifier=odoo13
PermissionsStartOnly=true
User=odoo
Group=odoo
ExecStart=/usr/bin/scl enable rh-python35 -- /opt/odoo/odoo13-venv/bin/python3 /opt/odoo/odoo13/odoo-bin -c /etc/odoo.conf
StandardOutput=journal+console

[Install]
WantedBy=multi-user.target

Now, save the file and exit.

Now, you need to reload the systemd daemon to be able to read the latest changes you have made to services. To do that, run:

systemctl daemon-reload

Finally, now we can start Odoo 13 instance using the following command:

systemctl start odoo13

If you are interested to check the status of the instance, you may do this:

systemctl status odoo13
[[email protected] ~]# systemctl status odoo13
● odoo13.service - Odoo13
   Loaded: loaded (/etc/systemd/system/odoo13.service; enabled; vendor preset: disabled)
   Active: active (running) since Sun 2020-09-13 08:26:46 EDT; 23h ago
 Main PID: 24502 (scl)
   CGroup: /system.slice/odoo13.service
           ├─24502 /usr/bin/scl enable rh-python36 -- /opt/odoo/odoo13-venv/bin/python3 /opt/odoo/odoo13/odoo-bin -c /etc/odoo.conf
           ├─24503 /bin/bash /var/tmp/sclSWH04z
           └─24507 /opt/odoo/odoo13-venv/bin/python3 /opt/odoo/odoo13/odoo-bin -c /etc/odoo.conf

It show green active running, if everything worked out. If you see no error, you may now enable Odoo to start during the boot:

systemctl enable odoo13

If you would like to see the logs, you may either use the journal tools like the following:

journalctl -u odoo13

or uncomment the following line to log the debugs in /etc/odoo.conf

logfile = /var/log/odoo13/odoo.log

After making any change to /etc/odoo.conf, do not forget the restart the Odoo13 instance using systemctl.

Test the Installation

You may now test the installation using http://your_server_ip:8069. If everything worked, it should come up. If it doesn’t, you may try stopping your ‘firewalld’ to see if firewall is blocking the port or not:

systemctl stop firewalld

At Mellowhost, we provide Odoo installation and configuration assistance for absolute free of charge. If you are willing to try out any of our VPS for Odoo, you may do so and talk with us through the Live chat or the ticket for Odoo assistance.

Furthermore, Good luck.

Odoo Controller JSON Route Returns 404 – werkzeug.exceptions.NotFound

Even though, if you have defined your routes properly, you are seeing an error of the following:

{
    "id": null,
    "jsonrpc": "2.0",
    "error": {
        "http_status": 404,
        "code": 404,
        "data": {
            "name": "werkzeug.exceptions.NotFound",
            "debug": "Traceback (most recent call last):\n  File \"/opt/odoo/odoo12/odoo/http.py\", line 656, in _handle_exception\n    return super(JsonRequest, self)._handle_exception(exception)\n  File \"/opt/odoo/odoo12/odoo/http.py\", line 314, in _handle_exception\n    raise pycompat.reraise(type(exception), exception, sys.exc_info()[2])\n  File \"/opt/odoo/odoo12/odoo/tools/pycompat.py\", line 87, in reraise\n    raise value\n  File \"/opt/odoo/odoo12/odoo/http.py\", line 1460, in _dispatch_nodb\n    func, arguments = self.nodb_routing_map.bind_to_environ(request.httprequest.environ).match()\n  File \"/opt/odoo/odoo12-venv/lib64/python3.5/site-packages/werkzeug/routing.py\", line 1563, in match\n    raise NotFound()\nwerkzeug.exceptions.NotFound: 404: Not Found\n",
            "message": "404: Not Found",
            "exception_type": "internal_error",
            "arguments": []
        },
        "message": "404: Not Found"
    }
}

This error doesn’t return when you use the http.route as type ‘http’ or default, which is still http, but returns when you use the type ‘json’. One of the cause why the error return is that, you have multiple Odoo databases and Odoo is failing to detect the usable database for the type json. For the type, http, Odoo usually can predict what to use, while for the type json, it can not. For such cases, you would need to use the ‘db-filter’ to add the default database to load for Odoo on the odoo-bin command. If you are using the systemd service file, append the line with the following:

--db-filter=^my_prod$

where ‘my_prod’ is your database name.

So the service ExecStart would look like the following:

ExecStart=/usr/bin/scl enable rh-python35 -- /opt/odoo/odoo12-venv/bin/python3 /opt/odoo/odoo12/odoo-bin -c /etc/odoo.conf --limit-time-real 1009999999 --limit-time-cpu 1009999999 --limit-memory-hard 89179869184000000 --limit-memory-soft 57179869184000000 --db-filter=^my_prod$

After making the change, reload your systemctl and restart your odoo-bin:

systemctl daemon-reload
service odoo12 restart

This should do the job.

Dirty Odoo Hacks: How to Know If a Scheduled Action is Running

This blog post goes to my ‘Dirty Hack’ series, where I try to open the gross hacking attempts I practically use in several of my projects.

Odoo is my favorite piece of framework. Even though it has evolved as OpenERP, but the extend ability that Odoo gives, probably can take it anywhere, any other framework unable to, without massive change to their base architecture. This goes to the pros of Odoo, while the biggest cons of Odoo, is that it is not owned/developed by a major ‘English’ speaking company/people. If you go through the Odoo documentation, you can clearly understand the difference I am talking about. One key reason, Odoo is not often seen everywhere, probably because of poor documentation. Most of the things are not explained in details, as Odoo is a large piece of software, makes it difficult for developers to dig it down and extend.

Odoo has it’s own scheduling system, which they call ‘Scheduled Actions’. Odoo manages a model ‘ir.cron’ to manage the Scheduled Actions. If you look at the model details from (on debug mode)
Odoo >> Settings >> Technical >> Database Structure >> Model
you will see, there is no field it uses to detect if the scheduled action is running or stopped. It has a ‘state’ property though, which does not mean the status like many other odoo models. Here is a output of state property from Odoo shell:

>>> cron_id = self.env['ir.cron'].browse(18)
>>> cron_id.state

'code'

That says, it is telling you what kind of operation it is going to execute, here it is saying, this scheduled action is executing a ‘Python Code’ here.

There are times, when you might require to determine whether the scheduled action is running or not. In my case, it was to track down the some syncing issues, like to find out whether the syncing is doing it’s job properly through some custom methods. As Odoo doesn’t provide a way to do this, I had to find a dirty way to do it. Here is how I did it.

Odoo runs in Postgres SQL, which is basically an open source version of Oracle like Object Oriented Database system. Like many other OODS, Postgres also provide an operation called the following:

FOR UPDATE NOWAIT

From the oracle documentation, it means:

Oracle provides the FOR UPDATE NOWAIT clause in SQL syntax to allow the developer to lock a set of Oracle rows for the duration of a transaction.

When the scheduled action runs, it locks the specific row of the ir_cron table, of course it would because it has to update that specific row with nextcall and other field data. So if a cron is running, and you try to put a lock with another process, will basically fail, which means the cron is running, otherwise the opposite is true. Viola, that should work for us, isn’t it? Simple! Then again, you have to remember, the lock will put in place for the amount of time you specify, or you have to throw an early rollback (why not a commit? to be in safety to avoid any unwanted data being committed to the database during that session). Here is the simple method to do the whole process:

def _check_if_cron_running(self):
cron_id = self.env['ir.cron'].browse(18)
""" assuming the cron id is 18, you can do any kind of search, like searching by name of the scheduled action etc """
if cron_id:
try:
self._cr.execute("""SELECT id FROM "%s" WHERE id IN %%s FOR UPDATE NOWAIT""" % cron_id._table, [tuple(cron_id.ids)], log_exceptions=False)
""" self._cr.execute is used to execute a direct sql command, each ir.cron model data will have 'ids' to tell you which ids are selected, and _table to tell you what is the table name for the model, here it should return ir_cron """
self._cr.rollback() # we need to rollback and give the database cursor back to the other process ASAP
_logger.info("log and operate whatever here, this section is reached when the scheduled action is stopped or not running")
except psycopg2.OperationalError:
self._cr.rollback() # we need to rollback the errors and give control to the other process
_logger.info("log and operate whatever here, this section is reached when the scheduled action is running")

Remember, this is dirty, you are hitting direct SQL, on a near around 500 model based framework. So, please take care of your things before placing this in production. Rest assured, keep using Odoo for future! A brilliant piece of art is Odoo!