# Copyright (C) 2017 - Today: GRAP (http://www.grap.coop)
# @author: Sylvain LE GAL (https://twitter.com/legalsylvain)
# License AGPL-3.0 or later (http://www.gnu.org/licenses/agpl.html).
import logging
from datetime import datetime, timedelta
from psycopg2 import ProgrammingError
from flectra import SUPERUSER_ID, _, api, fields, models
from flectra.exceptions import UserError, ValidationError
from flectra.tools import sql, table_columns
from flectra.tools.safe_eval import safe_eval
_logger = logging.getLogger(__name__)
class BiSQLView(models.Model):
_name = "bi.sql.view"
_description = "BI SQL View"
_order = "sequence"
_inherit = ["sql.request.mixin"]
_sql_prefix = "x_bi_sql_view_"
_model_prefix = "x_bi_sql_view."
_sql_request_groups_relation = "bi_sql_view_groups_rel"
_sql_request_users_relation = "bi_sql_view_users_rel"
_STATE_SQL_EDITOR = [
("model_valid", "SQL View and Model Created"),
("ui_valid", "Views, Action and Menu Created"),
]
technical_name = fields.Char(
required=True,
help="Suffix of the SQL view. SQL full name will be computed and"
" prefixed by 'x_bi_sql_view_'. Syntax should follow: "
"https://www.postgresql.org/"
"docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS",
)
view_name = fields.Char(
compute="_compute_view_name",
readonly=True,
store=True,
help="Full name of the SQL view",
)
model_name = fields.Char(
compute="_compute_model_name",
readonly=True,
store=True,
help="Full Qualified Name of the transient model that will" " be created.",
)
is_materialized = fields.Boolean(
string="Is Materialized View",
default=True,
)
materialized_text = fields.Char(compute="_compute_materialized_text", store=True)
size = fields.Char(
string="Database Size",
readonly=True,
help="Size of the materialized view and its indexes",
)
state = fields.Selection(selection_add=_STATE_SQL_EDITOR)
view_order = fields.Char(
required=True,
default="pivot,graph,tree",
help="Comma-separated text. Possible values:"
' "graph", "pivot", "tree" or "form"',
)
query = fields.Text(
help="SQL Request that will be inserted as the view. Take care to :\n"
" * set a name for all your selected fields, specially if you use"
" SQL function (like EXTRACT, ...);\n"
" * Do not use 'SELECT *' or 'SELECT table.*';\n"
" * prefix the name of the selectable columns by 'x_';",
default="SELECT\n" " my_field as x_my_field\n" "FROM my_table",
)
domain_force = fields.Text(
string="Extra Rule Definition",
default="[]",
help="Define here access restriction to data.\n"
" Take care to use field name prefixed by 'x_'."
" A global 'ir.rule' will be created."
" A typical Multi Company rule is for exemple \n"
" ['|', ('x_company_id','child_of', [user.company_id.id]),"
"('x_company_id','=',False)].",
)
computed_action_context = fields.Text(compute="_compute_computed_action_context")
action_context = fields.Text(
default="{}",
help="Define here a context that will be used"
" by default, when creating the action.",
)
bi_sql_view_field_ids = fields.One2many(
string="SQL Fields",
comodel_name="bi.sql.view.field",
inverse_name="bi_sql_view_id",
)
model_id = fields.Many2one(
string="Flectra Model", comodel_name="ir.model", readonly=True
)
# UI related fields
# 1. Editable fields, which can be set by the user (optional) before
# creating the UI elements
@api.model
def _default_parent_menu_id(self):
return self.env.ref("bi_sql_editor.menu_bi_sql_editor")
parent_menu_id = fields.Many2one(
string="Parent Flectra Menu",
comodel_name="ir.ui.menu",
required=True,
default=lambda self: self._default_parent_menu_id(),
help="By assigning a value to this field before manually creating the "
"UI, you're overwriting the parent menu on which the menu related to "
"the SQL report will be created.",
)
# 2. Readonly fields, non editable by the user
form_view_id = fields.Many2one(
string="Flectra Form View", comodel_name="ir.ui.view", readonly=True
)
tree_view_id = fields.Many2one(
string="Flectra Tree View", comodel_name="ir.ui.view", readonly=True
)
graph_view_id = fields.Many2one(
string="Flectra Graph View", comodel_name="ir.ui.view", readonly=True
)
pivot_view_id = fields.Many2one(
string="Flectra Pivot View", comodel_name="ir.ui.view", readonly=True
)
search_view_id = fields.Many2one(
string="Flectra Search View", comodel_name="ir.ui.view", readonly=True
)
action_id = fields.Many2one(
string="Flectra Action", comodel_name="ir.actions.act_window", readonly=True
)
menu_id = fields.Many2one(
string="Flectra Menu", comodel_name="ir.ui.menu", readonly=True
)
cron_id = fields.Many2one(
string="Flectra Cron",
comodel_name="ir.cron",
readonly=True,
help="Cron Task that will refresh the materialized view",
ondelete="cascade",
)
rule_id = fields.Many2one(string="Flectra Rule", comodel_name="ir.rule", readonly=True)
sequence = fields.Integer(string="sequence")
# Constrains Section
@api.constrains("is_materialized")
def _check_index_materialized(self):
for rec in self.filtered(lambda x: not x.is_materialized):
if rec.bi_sql_view_field_ids.filtered(lambda x: x.is_index):
raise UserError(
_("You can not create indexes on non materialized views")
)
@api.constrains("view_order")
def _check_view_order(self):
for rec in self:
if rec.view_order:
for vtype in rec.view_order.split(","):
if vtype not in ("graph", "pivot", "tree", "form"):
raise UserError(
_("Only graph, pivot, tree or form views are supported")
)
# Compute Section
@api.depends("bi_sql_view_field_ids.graph_type")
def _compute_computed_action_context(self):
for rec in self:
action = {
"pivot_measures": [],
"pivot_row_groupby": [],
"pivot_column_groupby": [],
}
for field in rec.bi_sql_view_field_ids.filtered(
lambda x: x.graph_type == "measure"
):
action["pivot_measures"].append(field.name)
# If no measure are defined, we display by default the count
# of the element, to avoid an empty view
if not action["pivot_measures"]:
action["pivot_measures"] = ["__count__"]
for field in rec.bi_sql_view_field_ids.filtered(
lambda x: x.graph_type == "row"
):
action["pivot_row_groupby"].append(field.name)
for field in rec.bi_sql_view_field_ids.filtered(
lambda x: x.graph_type == "col"
):
action["pivot_column_groupby"].append(field.name)
rec.computed_action_context = str(action)
@api.depends("is_materialized")
def _compute_materialized_text(self):
for sql_view in self:
sql_view.materialized_text = (
sql_view.is_materialized and "MATERIALIZED" or ""
)
@api.depends("technical_name")
def _compute_view_name(self):
for sql_view in self:
sql_view.view_name = f"{sql_view._sql_prefix}{sql_view.technical_name}"
@api.depends("technical_name")
def _compute_model_name(self):
for sql_view in self:
sql_view.model_name = f"{sql_view._model_prefix}{sql_view.technical_name}"
# Overload Section
def write(self, vals):
res = super().write(vals)
if vals.get("sequence", False):
for rec in self.filtered(lambda x: x.menu_id):
rec.menu_id.sequence = rec.sequence
return res
def unlink(self):
if any(view.state not in ("draft", "sql_valid") for view in self):
raise UserError(
_(
"You can only unlink draft views."
"If you want to delete them, first set them to draft."
)
)
return super().unlink()
def copy(self, default=None):
self.ensure_one()
default = dict(default or {})
if "name" not in default:
default["name"] = _("%s (Copy)") % self.name
if "technical_name" not in default:
default["technical_name"] = f"{self.technical_name}_copy"
return super().copy(default=default)
# Action Section
def button_create_sql_view_and_model(self):
for sql_view in self.filtered(lambda x: x.state == "sql_valid"):
# Check if many2one fields are correctly
bad_fields = sql_view.bi_sql_view_field_ids.filtered(
lambda x: x.ttype == "many2one" and not x.many2one_model_id.id
)
if bad_fields:
raise ValidationError(
_("Please set related models on the following fields %s")
% ",".join(bad_fields.mapped("name"))
)
# Create ORM and access
sql_view._create_model_and_fields()
sql_view._create_model_access()
# Create SQL View and indexes
sql_view._create_view()
sql_view._create_index()
if sql_view.is_materialized:
if not sql_view.cron_id:
sql_view.cron_id = (
self.env["ir.cron"].create(sql_view._prepare_cron()).id
)
else:
sql_view.cron_id.active = True
sql_view.state = "model_valid"
def button_reset_to_model_valid(self):
views = self.filtered(lambda x: x.state == "ui_valid")
views.mapped("form_view_id").unlink()
views.mapped("tree_view_id").unlink()
views.mapped("graph_view_id").unlink()
views.mapped("pivot_view_id").unlink()
views.mapped("search_view_id").unlink()
views.mapped("action_id").unlink()
views.mapped("menu_id").unlink()
return views.write({"state": "model_valid"})
def button_reset_to_sql_valid(self):
self.button_reset_to_model_valid()
views = self.filtered(lambda x: x.state == "model_valid")
for sql_view in views:
# Drop SQL View (and indexes by cascade)
if sql_view.is_materialized:
sql_view._drop_view()
if sql_view.cron_id:
sql_view.cron_id.active = False
# Drop ORM
sql_view._drop_model_and_fields()
return views.write({"state": "sql_valid"})
def button_set_draft(self):
self.button_reset_to_model_valid()
self.button_reset_to_sql_valid()
return super().button_set_draft()
def button_create_ui(self):
self.form_view_id = self.env["ir.ui.view"].create(self._prepare_form_view()).id
self.tree_view_id = self.env["ir.ui.view"].create(self._prepare_tree_view()).id
self.graph_view_id = (
self.env["ir.ui.view"].create(self._prepare_graph_view()).id
)
self.pivot_view_id = (
self.env["ir.ui.view"].create(self._prepare_pivot_view()).id
)
self.search_view_id = (
self.env["ir.ui.view"].create(self._prepare_search_view()).id
)
self.action_id = (
self.env["ir.actions.act_window"].create(self._prepare_action()).id
)
self.menu_id = self.env["ir.ui.menu"].create(self._prepare_menu()).id
self.write({"state": "ui_valid"})
def button_update_model_access(self):
self._drop_model_access()
self._create_model_access()
self.write({"has_group_changed": False})
def button_refresh_materialized_view(self):
self._refresh_materialized_view()
def button_open_view(self):
return {
"type": "ir.actions.act_window",
"res_model": self.model_id.model,
"search_view_id": self.search_view_id.id,
"view_mode": self.action_id.view_mode,
}
# Prepare Function
def _prepare_model(self):
self.ensure_one()
field_id = []
for field in self.bi_sql_view_field_ids.filtered(
lambda x: x.field_description is not False
):
field_id.append([0, False, field._prepare_model_field()])
return {
"name": self.name,
"model": self.model_name,
"access_ids": [],
"field_id": field_id,
}
def _prepare_model_access(self):
self.ensure_one()
res = []
for group in self.group_ids:
res.append(
{
"name": _("%(model_name)s Access %(full_name)s")
% {"model_name": self.model_name, "full_name": group.full_name},
"model_id": self.model_id.id,
"group_id": group.id,
"perm_read": True,
"perm_create": False,
"perm_write": False,
"perm_unlink": False,
}
)
return res
def _prepare_cron(self):
now = datetime.now()
return {
"name": _("Refresh Materialized View %s") % self.view_name,
"user_id": SUPERUSER_ID,
"model_id": self.env["ir.model"]
.search([("model", "=", self._name)], limit=1)
.id,
"state": "code",
"code": "model._refresh_materialized_view_cron(%s)" % self.ids,
"numbercall": -1,
"interval_number": 1,
"interval_type": "days",
"nextcall": now + timedelta(days=1),
"active": True,
}
def _prepare_rule(self):
self.ensure_one()
return {
"name": _("Access %s") % self.name,
"model_id": self.model_id.id,
"domain_force": self.domain_force,
"global": True,
}
def _prepare_form_view(self):
self.ensure_one()
return {
"name": self.name,
"type": "form",
"model": self.model_id.model,
"arch": """"""
"""""".format(
"".join([x._prepare_form_field() for x in self.bi_sql_view_field_ids])
),
}
def _prepare_tree_view(self):
self.ensure_one()
return {
"name": self.name,
"type": "tree",
"model": self.model_id.model,
"arch": """"""
"""{}"""
"""""".format(
"".join([x._prepare_tree_field() for x in self.bi_sql_view_field_ids])
),
}
def _prepare_graph_view(self):
self.ensure_one()
return {
"name": self.name,
"type": "graph",
"model": self.model_id.model,
"arch": """"""
"""{}"""
"""""".format(
"".join([x._prepare_graph_field() for x in self.bi_sql_view_field_ids])
),
}
def _prepare_pivot_view(self):
self.ensure_one()
return {
"name": self.name,
"type": "pivot",
"model": self.model_id.model,
"arch": """"""
"""{}"""
"""""".format(
"".join([x._prepare_pivot_field() for x in self.bi_sql_view_field_ids])
),
}
def _prepare_search_view(self):
self.ensure_one()
return {
"name": self.name,
"type": "search",
"model": self.model_id.model,
"arch": """"""
"""{}"""
"""{}"""
"""""".format(
"".join(
[x._prepare_search_field() for x in self.bi_sql_view_field_ids]
),
"".join(
[
x._prepare_search_filter_field()
for x in self.bi_sql_view_field_ids
]
),
),
}
def _prepare_action(self):
self.ensure_one()
view_mode = self.view_order
first_view = view_mode.split(",")[0]
if first_view == "form":
view_id = self.form_view_id.id
if first_view == "tree":
view_id = self.tree_view_id.id
elif first_view == "pivot":
view_id = self.pivot_view_id.id
else:
view_id = self.graph_view_id.id
action = safe_eval(self.computed_action_context)
for k, v in safe_eval(self.action_context).items():
action[k] = v
return {
"name": self._prepare_action_name(),
"res_model": self.model_id.model,
"type": "ir.actions.act_window",
"view_mode": view_mode,
"view_id": view_id,
"search_view_id": self.search_view_id.id,
"context": str(action),
}
def _prepare_action_name(self):
self.ensure_one()
if not self.is_materialized:
return self.name
return "{} ({})".format(
self.name,
datetime.utcnow().strftime("%m/%d/%Y %H:%M:%S UTC"),
)
def _prepare_menu(self):
self.ensure_one()
return {
"name": self.name,
"parent_id": self.parent_menu_id.id,
"action": "ir.actions.act_window,%s" % self.action_id.id,
"sequence": self.sequence,
}
# Custom Section
def _log_execute(self, req):
_logger.info("Executing SQL Request %s ..." % req)
self.env.cr.execute(req)
def _drop_view(self):
for sql_view in self:
self._log_execute(
f"DROP {sql_view.materialized_text} VIEW IF EXISTS {sql_view.view_name}"
)
sql_view.size = False
def _create_view(self):
for sql_view in self:
sql_view._drop_view()
try:
self._log_execute(sql_view._prepare_request_for_execution())
sql_view._refresh_size()
except ProgrammingError as e:
raise UserError(
_(
"SQL Error while creating %(materialized_text)s"
" VIEW %(view_name)s :\n %(error)s"
)
% {
"materialized_text": sql_view.materialized_text,
"view_name": sql_view.view_name,
"error": str(e),
}
) from e
def _create_index(self):
for sql_view in self:
for sql_field in sql_view.bi_sql_view_field_ids.filtered(
lambda x: x.is_index is True
):
self._log_execute(
f"CREATE INDEX {sql_field.index_name} ON {sql_view.view_name} "
f"({sql_field.name});"
)
def _create_model_and_fields(self):
for sql_view in self:
# Create model
sql_view.model_id = self.env["ir.model"].create(self._prepare_model()).id
sql_view.rule_id = self.env["ir.rule"].create(self._prepare_rule()).id
# Drop table, created by the ORM
if sql.table_exists(self._cr, sql_view.view_name):
req = "DROP TABLE %s" % sql_view.view_name
self._log_execute(req)
def _create_model_access(self):
for sql_view in self:
for item in sql_view._prepare_model_access():
self.env["ir.model.access"].create(item)
def _drop_model_access(self):
for sql_view in self:
self.env["ir.model.access"].search(
[("model_id", "=", sql_view.model_name)]
).unlink()
def _drop_model_and_fields(self):
for sql_view in self:
if sql_view.rule_id:
sql_view.rule_id.unlink()
if sql_view.model_id:
sql_view.model_id.with_context(_force_unlink=True).unlink()
def _hook_executed_request(self):
self.ensure_one()
req = (
"""
SELECT attnum,
attname AS column,
format_type(atttypid, atttypmod) AS type
FROM pg_attribute
WHERE attrelid = '%s'::regclass
AND NOT attisdropped
AND attnum > 0
ORDER BY attnum;"""
% self.view_name
)
self._log_execute(req)
return self.env.cr.fetchall()
def _prepare_request_check_execution(self):
self.ensure_one()
return f"CREATE VIEW {self.view_name} AS ({self.query});"
def _prepare_request_for_execution(self):
self.ensure_one()
query = (
"""
SELECT
CAST(row_number() OVER () as integer) AS id,
CAST(Null as timestamp without time zone) as create_date,
CAST(Null as integer) as create_uid,
CAST(Null as timestamp without time zone) as write_date,
CAST(Null as integer) as write_uid,
my_query.*
FROM
(%s) as my_query
"""
% self.query
)
return f"CREATE {self.materialized_text} VIEW {self.view_name} AS ({query});"
def _check_execution(self):
"""Ensure that the query is valid, trying to execute it.
a non materialized view is created for this check.
A rollback is done at the end.
After the execution, and before the rollback, an analysis of
the database structure is done, to know fields type."""
self.ensure_one()
sql_view_field_obj = self.env["bi.sql.view.field"]
columns = super()._check_execution()
field_ids = []
for column in columns:
existing_field = self.bi_sql_view_field_ids.filtered(
lambda x, c=column: x.name == c[1]
)
if existing_field:
# Update existing field
field_ids.append(existing_field.id)
existing_field.write({"sequence": column[0], "sql_type": column[2]})
else:
# Create a new one if name is prefixed by x_
if column[1][:2] == "x_":
field_ids.append(
sql_view_field_obj.create(
{
"sequence": column[0],
"name": column[1],
"sql_type": column[2],
"bi_sql_view_id": self.id,
}
).id
)
# Drop obsolete view field
self.bi_sql_view_field_ids.filtered(lambda x: x.id not in field_ids).unlink()
if not self.bi_sql_view_field_ids:
raise UserError(
_("No Column was found.\n" "Columns name should be prefixed by 'x_'.")
)
return columns
@api.model
def _refresh_materialized_view_cron(self, view_ids):
sql_views = self.search(
[
("is_materialized", "=", True),
("state", "in", ["model_valid", "ui_valid"]),
("id", "in", view_ids),
]
)
return sql_views._refresh_materialized_view()
def _refresh_materialized_view(self):
for sql_view in self.filtered(lambda x: x.is_materialized):
req = f"REFRESH {sql_view.materialized_text} VIEW {sql_view.view_name}"
self._log_execute(req)
sql_view._refresh_size()
if sql_view.action_id:
# Alter name of the action, to display last refresh
# datetime of the materialized view
sql_view.action_id.with_context(
lang=self.env.user.lang
).name = sql_view._prepare_action_name()
def _refresh_size(self):
for sql_view in self:
req = "SELECT pg_size_pretty(pg_total_relation_size('%s'));" % (
sql_view.view_name
)
self._log_execute(req)
sql_view.size = self.env.cr.fetchone()[0]
def check_manual_fields(self, model):
# check the fields we need are defined on self, to stop it going
# early on install / startup - particularly problematic during upgrade
if model._name.startswith(
self._model_prefix
) and "group_operator" in table_columns(self.env.cr, "bi_sql_view_field"):
# Use SQL instead of ORM, as ORM might not be fully initialised -
# we have no control over the order that fields are defined!
# We are not concerned about user security rules.
self.env.cr.execute(
"""
SELECT
f.name,
f.ttype,
f.group_operator
FROM
bi_sql_view v
LEFT JOIN bi_sql_view_field f ON f.bi_sql_view_id = v.id
WHERE
v.model_name = %s
;
""",
(model._name,),
)
sql_fields = self.env.cr.fetchall()
for sql_field in sql_fields:
if (
sql_field[0] in model._fields
and sql_field[1] in ("integer", "float")
and sql_field[2]
):
model._fields[sql_field[0]].group_operator = sql_field[2]
def button_preview_sql_expression(self):
self.button_validate_sql_expression()
res = self._execute_sql_request()
raise UserError("\n".join(map(lambda x: str(x), res[:100])))