openerp-community-reviewer team mailing list archive
-
openerp-community-reviewer team
-
Mailing list archive
-
Message #05569
[Merge] lp:~florian-dacosta/server-env-tools/sql_query_export_csv_file into lp:server-env-tools
Florian da Costa has proposed merging lp:~florian-dacosta/server-env-tools/sql_query_export_csv_file into lp:server-env-tools.
Requested reviews:
Sébastien BEAU - http://www.akretion.com (sebastien.beau)
For more details, see:
https://code.launchpad.net/~florian-dacosta/server-env-tools/sql_query_export_csv_file/+merge/213826
Add a module named sql_export which allow a group of user to create and edit a new object with a sql query.
This query can be executed by the chosen groups or users in order to export the data of the query in a CSV file.
--
https://code.launchpad.net/~florian-dacosta/server-env-tools/sql_query_export_csv_file/+merge/213826
Your team Server Environment And Tools Core Editors is subscribed to branch lp:server-env-tools.
=== added directory 'sql_export'
=== added file 'sql_export/__init__.py'
--- sql_export/__init__.py 1970-01-01 00:00:00 +0000
+++ sql_export/__init__.py 2014-04-02 12:02:27 +0000
@@ -0,0 +1,24 @@
+# -*- coding: utf-8 -*-
+###############################################################################
+#
+# action_server_email for OpenERP
+# Copyright (C) 2013-TODAY Akretion <http://www.akretion.com>.
+# @author Florian DA COSTA <florian.dacosta@xxxxxxxxxxxx>
+#
+# This program is free software: you can redistribute it and/or modify
+# it under the terms of the GNU Affero General Public License as
+# published by the Free Software Foundation, either version 3 of the
+# License, or (at your option) any later version.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# GNU Affero General Public License for more details.
+#
+# You should have received a copy of the GNU Affero General Public License
+# along with this program. If not, see <http://www.gnu.org/licenses/>.
+#
+###############################################################################
+
+import sql
+import wizard
=== added file 'sql_export/__openerp__.py'
--- sql_export/__openerp__.py 1970-01-01 00:00:00 +0000
+++ sql_export/__openerp__.py 2014-04-02 12:02:27 +0000
@@ -0,0 +1,49 @@
+# -*- coding: utf-8 -*-
+###############################################################################
+#
+# action_server_email for OpenERP
+# Copyright (C) 2013-TODAY Akretion <http://www.akretion.com>.
+# @author Florian DA COSTA <florian.dacosta@xxxxxxxxxxxx>
+#
+# This program is free software: you can redistribute it and/or modify
+# it under the terms of the GNU Affero General Public License as
+# published by the Free Software Foundation, either version 3 of the
+# License, or (at your option) any later version.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# GNU Affero General Public License for more details.
+#
+# You should have received a copy of the GNU Affero General Public License
+# along with this program. If not, see <http://www.gnu.org/licenses/>.
+#
+###############################################################################
+
+
+{
+ 'name': 'SQL Export',
+ 'version': '0.1',
+ 'category': 'Generic Modules/Others',
+ 'license': 'AGPL-3',
+ 'description':
+ """
+ Allow to execute sql query from openerp interface in order to export datas in csv files.
+ To edit or create a query, the user have to be in sql_query editor group.
+ To execute a query, the user or group of user have to be specified in the sql_export record.
+ """,
+ 'author': 'Akretion',
+ 'website': 'http://www.akretion.com/',
+ 'depends': [
+ "base",
+ ],
+ 'init_xml': [],
+ 'update_xml': [
+ 'sql_view.xml',
+ 'wizard/wizard_file_view.xml',
+ 'security/sql_export_security.xml',
+ 'security/ir.model.access.csv',
+ ],
+ 'demo_xml': [],
+ 'installable': True,
+}
=== added directory 'sql_export/i18n'
=== added file 'sql_export/i18n/fr.po'
--- sql_export/i18n/fr.po 1970-01-01 00:00:00 +0000
+++ sql_export/i18n/fr.po 2014-04-02 12:02:27 +0000
@@ -0,0 +1,132 @@
+# Translation of OpenERP Server.
+# This file contains the translation of the following modules:
+# * sql_export
+#
+msgid ""
+msgstr ""
+"Project-Id-Version: OpenERP Server 7.0\n"
+"Report-Msgid-Bugs-To: \n"
+"POT-Creation-Date: 2014-04-01 08:39+0000\n"
+"PO-Revision-Date: 2014-04-01 08:39+0000\n"
+"Last-Translator: <>\n"
+"Language-Team: \n"
+"MIME-Version: 1.0\n"
+"Content-Type: text/plain; charset=UTF-8\n"
+"Content-Transfer-Encoding: \n"
+"Plural-Forms: \n"
+
+#. module: sql_export
+#: model:ir.ui.menu,name:sql_export.menu_export
+msgid "Export"
+msgstr "Export"
+
+#. module: sql_export
+#: code:addons/sql_export/sql.py:103
+#: code:addons/sql_export/sql.py:114
+#, python-format
+msgid "Invalid Query"
+msgstr "Requête Invalide"
+
+#. module: sql_export
+#: model:res.groups,name:sql_export.group_sql_request_editor
+msgid "Sql Request Editor"
+msgstr "Edition de Requête SQL"
+
+#. module: sql_export
+#: field:sql.export,name:0
+msgid "Name"
+msgstr "Nom"
+
+#. module: sql_export
+#: model:ir.model,name:sql_export.model_sql_file_wizard
+msgid "Allow to the user to save the file with sql request's data"
+msgstr "Permet à l'utilisateur de sauvegarder le fichier contenant les données de la requête SQL"
+
+#. module: sql_export
+#: code:addons/sql_export/sql.py:104
+#: code:addons/sql_export/sql.py:115
+#, python-format
+msgid "The Sql query is not valid."
+msgstr "La requête SQL n'est pas valide"
+
+#. module: sql_export
+#: code:addons/sql_export/sql.py:72
+#, python-format
+msgid "No data"
+msgstr "Pas de données"
+
+#. module: sql_export
+#: model:ir.model,name:sql_export.model_sql_export
+#: view:sql.export:0
+msgid "SQL export"
+msgstr "SQL export"
+
+#. module: sql_export
+#: field:sql.file.wizard,file_name:0
+msgid "File Name"
+msgstr "Nom du fichier"
+
+#. module: sql_export
+#: model:ir.actions.act_window,name:sql_export.sql_export_tree_action
+#: view:sql.export:0
+msgid "SQL Export"
+msgstr "SQL Export"
+
+#. module: sql_export
+#: field:sql.export,group_ids:0
+msgid "Allowed Groups"
+msgstr "Groupes Autorisés"
+
+#. module: sql_export
+#: model:ir.ui.menu,name:sql_export.sql_export_menu
+#: model:ir.ui.menu,name:sql_export.sql_export_menu_view
+msgid "Sql Export"
+msgstr "Sql Export"
+
+#. module: sql_export
+#: constraint:sql.export:0
+msgid "The query you want make is not allowed : prohibited actions (Delete, drop...)"
+msgstr "La requête que vous voulez sauvegarder n'est pas autorisée : actions interdites (Delete, drop...)"
+
+#. module: sql_export
+#: view:sql.file.wizard:0
+msgid "Csv File"
+msgstr "Fichier CSV"
+
+#. module: sql_export
+#: view:sql.export:0
+msgid "Execute Query"
+msgstr "Exécuter"
+
+#. module: sql_export
+#: field:sql.file.wizard,file:0
+msgid "File"
+msgstr "Fichier"
+
+#. module: sql_export
+#: field:sql.export,query:0
+msgid "Query"
+msgstr "Query"
+
+#. module: sql_export
+#: view:sql.export:0
+msgid "Allowed Users Groups"
+msgstr "Groupes d'utilisateurs Autorisés"
+
+#. module: sql_export
+#: help:sql.export,query:0
+msgid "You can't use the following word : delete, drop, create, insert, update, alter, truncate, execute"
+msgstr "Vous ne pouvez pas utilisés les mots suivants : delete, drop, create, insert, update, alter, truncate, execute"
+
+#. module: sql_export
+#: code:addons/sql_export/sql.py:73
+#, python-format
+msgid "The query did not return any data."
+msgstr "La requête n'a retourné aucune donnée"
+
+#. module: sql_export
+#: view:sql.export:0
+#: field:sql.export,user_ids:0
+msgid "Allowed Users"
+msgstr "Utilisateurs Autorisés"
+
=== added directory 'sql_export/security'
=== added file 'sql_export/security/ir.model.access.csv'
--- sql_export/security/ir.model.access.csv 1970-01-01 00:00:00 +0000
+++ sql_export/security/ir.model.access.csv 2014-04-02 12:02:27 +0000
@@ -0,0 +1,3 @@
+"id","name","model_id:id","group_id:id","perm_read","perm_write","perm_create","perm_unlink"
+"access_sql_export_all","access_sql_export_all","model_sql_export",,1,0,0,0
+"access_sql_export_editor","access_sql_export_editor","model_sql_export",group_sql_request_editor,1,1,1,1
=== added file 'sql_export/security/sql_export_security.xml'
--- sql_export/security/sql_export_security.xml 1970-01-01 00:00:00 +0000
+++ sql_export/security/sql_export_security.xml 2014-04-02 12:02:27 +0000
@@ -0,0 +1,21 @@
+<?xml version="1.0" encoding="utf-8"?>
+<openerp>
+<data noupdate="0">
+
+ <record model="res.groups" id="group_sql_request_editor">
+ <field name="name">Sql Request Editor</field>
+ <field name="users" eval="[(4, ref('base.user_root'))]"/>
+ </record>
+
+ <record model="ir.rule" id="sql_export_restric_access_user_or_group">
+ <field name="name" >SQL Export users and groups rules</field>
+ <field name="model_id" ref="model_sql_export"/>
+ <field eval="1" name="perm_read"/>
+ <field eval="0" name="perm_create"/>
+ <field eval="0" name="perm_write"/>
+ <field eval="0" name="perm_unlink"/>
+ <field name="domain_force">['|', ('user_ids','=',user.id), ('group_ids','=', [x.id for x in user.groups_id])]</field>
+ </record>
+
+</data>
+</openerp>
=== added file 'sql_export/sql.py'
--- sql_export/sql.py 1970-01-01 00:00:00 +0000
+++ sql_export/sql.py 2014-04-02 12:02:27 +0000
@@ -0,0 +1,118 @@
+# -*- coding: utf-8 -*-
+###############################################################################
+#
+# action_server_email for OpenERP
+# Copyright (C) 2013-TODAY Akretion <http://www.akretion.com>.
+# @author Florian DA COSTA <florian.dacosta@xxxxxxxxxxxx>
+#
+# This program is free software: you can redistribute it and/or modify
+# it under the terms of the GNU Affero General Public License as
+# published by the Free Software Foundation, either version 3 of the
+# License, or (at your option) any later version.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# GNU Affero General Public License for more details.
+#
+# You should have received a copy of the GNU Affero General Public License
+# along with this program. If not, see <http://www.gnu.org/licenses/>.
+#
+###############################################################################
+
+from openerp.osv.orm import Model
+from openerp.osv import fields, orm
+from openerp.tools.translate import _
+import StringIO
+import csv
+import base64
+import time
+
+
+PROHIBITED_WORDS = ['delete', 'drop', 'create', 'insert', 'update', 'alter', 'truncate', 'execute']
+
+
+class SqlExport(Model):
+
+ _name = "sql.export"
+
+ _description = "SQL export"
+
+
+ def _check_query(self, cr, uid, ids, context=None):
+ for obj in self.browse(cr, uid, ids, context=context):
+ if any(word in obj.query.lower() for word in PROHIBITED_WORDS):
+ return False
+ if obj.query.split(' ')[0].lower() != 'select':
+ return False
+ return True
+
+
+ _columns = {
+ 'name': fields.char('Name', required=True),
+ 'query': fields.text('Query', required=True, help="You can't use the following word : delete, drop, create, insert, update, alter, truncate, execute"),
+ 'group_ids': fields.many2many('res.groups', 'groups_sqlquery_rel', 'sql_id', 'group_id', 'Allowed Groups'),
+ 'user_ids': fields.many2many('res.users', 'users_sqlquery_rel', 'sql_id', 'user_id', 'Allowed Users'),
+ }
+
+
+ _constraints = [(_check_query, 'The query you want make is not allowed : prohibited actions (Delete, drop...)', ['query'])]
+
+
+ def export_sql_query(self, cr, uid, ids, context=None):
+ if not context:
+ context = {}
+ for obj in self.browse(cr, uid, ids, context=context):
+ cr.execute(obj.query)
+ results = cr.dictfetchall()
+ if results:
+ header = results[0].keys()
+ values = [x.values() for x in results]
+ else:
+ raise orm.except_orm(_("No data"),
+ _("The query did not return any data."))
+ now = time.strftime('%Y-%m-%d',time.localtime())
+ output = StringIO.StringIO()
+ writer = csv.writer(output, quoting=csv.QUOTE_NONNUMERIC)
+ writer.writerow(header)
+ for value in values:
+ writer.writerow(value)
+ output.getvalue()
+ new_output = base64.b64encode(output.getvalue())
+ output.close()
+ wiz = self.pool.get('sql.file.wizard').create(cr, uid, {'file': new_output, 'file_name': obj.name + '_' + now + '.csv'})
+ cr.commit()
+ return {
+ 'view_type': 'form',
+ 'view_mode': 'form',
+ 'res_model': 'sql.file.wizard',
+ 'res_id': wiz,
+ 'type': 'ir.actions.act_window',
+ 'target': 'new',
+ 'context': context,
+ 'nodestroy': True,
+ }
+
+
+ def write(self, cr, uid, ids, vals, context=None):
+ if vals:
+ if 'query' in vals:
+ try:
+ cr.execute(vals['query'])
+ except:
+ raise orm.except_orm(_("Invalid Query"),
+ _("The Sql query is not valid."))
+ return super(SqlExport, self).write(cr, uid, ids, vals, context=context)
+
+
+ def create(self, cr, uid, vals, context=None):
+ if vals:
+ if 'query' in vals:
+ try:
+ cr.execute(vals['query'])
+ except:
+ raise orm.except_orm(_("Invalid Query"),
+ _("The Sql query is not valid."))
+ return super(SqlExport, self).create(cr, uid, vals, context=context)
+
+
=== added file 'sql_export/sql_view.xml'
--- sql_export/sql_view.xml 1970-01-01 00:00:00 +0000
+++ sql_export/sql_view.xml 2014-04-02 12:02:27 +0000
@@ -0,0 +1,63 @@
+<?xml version="1.0" encoding="utf-8"?>
+<openerp>
+<data>
+
+<menuitem name="Export"
+ id="menu_export"
+ groups="base.group_user"
+ sequence="190"/>
+
+
+
+
+
+
+ <record id="sql_export_view_form" model="ir.ui.view">
+ <field name="name">Sql_export_form_view</field>
+ <field name="model">sql.export</field>
+ <field name="arch" type="xml">
+ <form string="SQL export">
+ <group col="2">
+ <group colspan="2" col="5">
+ <label for="name" colspan="1"/>
+ <field name="name" colspan="2" nolabel="1"/>
+ <button name="export_sql_query" string="Execute Query" type="object" class="oe_highlight" icon="gtk-execute" colspan="2"/>
+ <label for="query" colspan="1"/>
+ <field name="query" nolabel="1" colspan="4"/>
+ </group>
+ <group colspan="2" col="2" groups="sql_export.group_sql_request_editor">
+ <separator string="Allowed Users" colspan="1"/>
+ <separator string="Allowed Users Groups" colspan="1"/>
+ <field name="user_ids" nolabel="1"/>
+ <field name="group_ids" nolabel="1"/>
+ </group>
+ </group>
+ </form>
+ </field>
+ </record>
+
+ <record id="sql_export_view_tree" model="ir.ui.view">
+ <field name="name">Sql_export_tree_view</field>
+ <field name="model">sql.export</field>
+ <field name="arch" type="xml">
+ <tree string="SQL Export">
+ <field name="name"/>
+ </tree>
+ </field>
+ </record>
+
+ <record id="sql_export_tree_action" model="ir.actions.act_window">
+ <field name="name">SQL Export</field>
+ <field name="res_model">sql.export</field>
+ <field name="view_type">form</field>
+ <field name="view_mode">tree,form</field>
+ </record>
+
+
+ <menuitem id="sql_export_menu" name="Sql Export" parent="menu_export" sequence="1"/>
+
+ <menuitem id="sql_export_menu_view" name="Sql Export" parent="sql_export_menu" action="sql_export_tree_action" sequence="1"/>
+
+
+</data>
+</openerp>
=== added directory 'sql_export/wizard'
=== added file 'sql_export/wizard/__init__.py'
--- sql_export/wizard/__init__.py 1970-01-01 00:00:00 +0000
+++ sql_export/wizard/__init__.py 2014-04-02 12:02:27 +0000
@@ -0,0 +1,23 @@
+# -*- coding: utf-8 -*-
+###############################################################################
+#
+# action_server_email for OpenERP
+# Copyright (C) 2013-TODAY Akretion <http://www.akretion.com>.
+# @author Florian DA COSTA <florian.dacosta@xxxxxxxxxxxx>
+#
+# This program is free software: you can redistribute it and/or modify
+# it under the terms of the GNU Affero General Public License as
+# published by the Free Software Foundation, either version 3 of the
+# License, or (at your option) any later version.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# GNU Affero General Public License for more details.
+#
+# You should have received a copy of the GNU Affero General Public License
+# along with this program. If not, see <http://www.gnu.org/licenses/>.
+#
+###############################################################################
+
+import wizard_file
=== added file 'sql_export/wizard/wizard_file.py'
--- sql_export/wizard/wizard_file.py 1970-01-01 00:00:00 +0000
+++ sql_export/wizard/wizard_file.py 2014-04-02 12:02:27 +0000
@@ -0,0 +1,41 @@
+# -*- coding: utf-8 -*-
+###############################################################################
+#
+# action_server_email for OpenERP
+# Copyright (C) 2013-TODAY Akretion <http://www.akretion.com>.
+# @author Florian DA COSTA <florian.dacosta@xxxxxxxxxxxx>
+#
+# This program is free software: you can redistribute it and/or modify
+# it under the terms of the GNU Affero General Public License as
+# published by the Free Software Foundation, either version 3 of the
+# License, or (at your option) any later version.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# GNU Affero General Public License for more details.
+#
+# You should have received a copy of the GNU Affero General Public License
+# along with this program. If not, see <http://www.gnu.org/licenses/>.
+#
+###############################################################################
+
+from openerp.osv.orm import Model
+from openerp.osv import fields, orm
+from openerp.tools.translate import _
+
+
+class SqlFileWizard(orm.TransientModel):
+
+ _name = "sql.file.wizard"
+
+ _description = "Allow to the user to save the file with sql request's data"
+
+
+ _columns = {
+ 'file': fields.binary('File', required=True, readonly=True),
+ 'file_name': fields.char('File Name', readonly=True),
+ }
+
+
+
=== added file 'sql_export/wizard/wizard_file_view.xml'
--- sql_export/wizard/wizard_file_view.xml 1970-01-01 00:00:00 +0000
+++ sql_export/wizard/wizard_file_view.xml 2014-04-02 12:02:27 +0000
@@ -0,0 +1,18 @@
+<?xml version="1.0" encoding="utf-8"?>
+<openerp>
+ <data>
+
+ <record id="sql_file_wizard_view_form" model="ir.ui.view">
+ <field name="name">sql.file.wizard.view.form</field>
+ <field name="model">sql.file.wizard</field>
+ <field name="arch" type="xml">
+ <form string="Csv File">
+ <field name="file" filename="file_name"/>
+ <field name="file_name" invisible="1"/>
+ </form>
+ </field>
+ </record>
+
+
+ </data>
+</openerp>
References
