Merge lp:~florian-dacosta/server-env-tools/sql_query_export_csv_file into lp:~server-env-tools-core-editors/server-env-tools/7.0

[Florian da Costa]

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.

[lp:~florian-dacosta/server-env-tools/sql_query_export_csv_file]

68. By Florian da Costa on 2014-04-02

[FIX] Fix encoding error in sql export module

69. By Florian da Costa on 2014-04-04

[FIX] Change logic and copy directly the result of the query to csv file

70. By Florian da Costa on 2014-04-04

[FIX] Fix previous commit mistake

Unmerged revisions

70. By Florian da Costa on 2014-04-04

[FIX] Fix previous commit mistake

69. By Florian da Costa on 2014-04-04

[FIX] Change logic and copy directly the result of the query to csv file

68. By Florian da Costa on 2014-04-02

[FIX] Fix encoding error in sql export module

67. By Florian da Costa on 2014-04-02

[ADD] Add sql export module : allow user to export data in csv file from a sql query

=== 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-04 14:08:54 +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 <[e-mail]>
+#
+#   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-04 14:08:54 +0000
@@ -0,0 +1,50 @@
+# -*- coding: utf-8 -*-
+###############################################################################
+#
+#   action_server_email for OpenERP
+#   Copyright (C) 2013-TODAY Akretion <http://www.akretion.com>.
+#   @author Florian DA COSTA <[e-mail]>
+#
+#   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-04 14:08:54 +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-04 14:08:54 +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-04 14:08:54 +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-04 14:08:54 +0000
@@ -0,0 +1,116 @@
+# -*- coding: utf-8 -*-
+###############################################################################
+#
+#   action_server_email for OpenERP
+#   Copyright (C) 2013-TODAY Akretion <http://www.akretion.com>.
+#   @author Florian DA COSTA <[e-mail]>
+#
+#   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', 'insert', '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
+
+    def _get_editor_group(self, cr, uid, *args):
+        gr_obj = self.pool.get('res.groups')
+        editors = gr_obj.search(cr, uid, [('name', '=', 'Sql Request Editor')])
+        return editors
+
+
+    _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'),
+    }
+
+
+    _defaults = {
+        'group_ids' : _get_editor_group,
+    }
+
+    _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):
+            now = time.strftime('%Y-%m-%d',time.localtime())
+            output = StringIO.StringIO()
+            query = "COPY (" + obj.query + ") TO STDOUT WITH CSV HEADER DELIMITER ';'"
+            cr.copy_expert(query, output)
+            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-04 14:08:54 +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-04 14:08:54 +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 <[e-mail]>
+#
+#   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-04 14:08:54 +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 <[e-mail]>
+#
+#   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-04 14:08:54 +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>