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

Proposed by Florian da Costa on 2014-04-02
Status: Work in progress
Proposed branch: lp:~florian-dacosta/server-env-tools/sql_query_export_csv_file
Merge into: lp:~server-env-tools-core-editors/server-env-tools/7.0
Diff against target: 544 lines (+491/-0)
10 files modified
sql_export/__init__.py (+24/-0)
sql_export/__openerp__.py (+50/-0)
sql_export/i18n/fr.po (+132/-0)
sql_export/security/ir.model.access.csv (+3/-0)
sql_export/security/sql_export_security.xml (+21/-0)
sql_export/sql.py (+116/-0)
sql_export/sql_view.xml (+63/-0)
sql_export/wizard/__init__.py (+23/-0)
sql_export/wizard/wizard_file.py (+41/-0)
sql_export/wizard/wizard_file_view.xml (+18/-0)
To merge this branch: bzr merge lp:~florian-dacosta/server-env-tools/sql_query_export_csv_file
Reviewer Review Type Date Requested Status
Sébastien BEAU - http://www.akretion.com 2014-04-02 Pending
Review via email: mp+213826@code.launchpad.net

Description of the change

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.

To post a comment you must log in.
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

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1=== added directory 'sql_export'
2=== added file 'sql_export/__init__.py'
3--- sql_export/__init__.py 1970-01-01 00:00:00 +0000
4+++ sql_export/__init__.py 2014-04-04 14:08:54 +0000
5@@ -0,0 +1,24 @@
6+# -*- coding: utf-8 -*-
7+###############################################################################
8+#
9+# action_server_email for OpenERP
10+# Copyright (C) 2013-TODAY Akretion <http://www.akretion.com>.
11+# @author Florian DA COSTA <florian.dacosta@akretion.com>
12+#
13+# This program is free software: you can redistribute it and/or modify
14+# it under the terms of the GNU Affero General Public License as
15+# published by the Free Software Foundation, either version 3 of the
16+# License, or (at your option) any later version.
17+#
18+# This program is distributed in the hope that it will be useful,
19+# but WITHOUT ANY WARRANTY; without even the implied warranty of
20+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
21+# GNU Affero General Public License for more details.
22+#
23+# You should have received a copy of the GNU Affero General Public License
24+# along with this program. If not, see <http://www.gnu.org/licenses/>.
25+#
26+###############################################################################
27+
28+import sql
29+import wizard
30
31=== added file 'sql_export/__openerp__.py'
32--- sql_export/__openerp__.py 1970-01-01 00:00:00 +0000
33+++ sql_export/__openerp__.py 2014-04-04 14:08:54 +0000
34@@ -0,0 +1,50 @@
35+# -*- coding: utf-8 -*-
36+###############################################################################
37+#
38+# action_server_email for OpenERP
39+# Copyright (C) 2013-TODAY Akretion <http://www.akretion.com>.
40+# @author Florian DA COSTA <florian.dacosta@akretion.com>
41+#
42+# This program is free software: you can redistribute it and/or modify
43+# it under the terms of the GNU Affero General Public License as
44+# published by the Free Software Foundation, either version 3 of the
45+# License, or (at your option) any later version.
46+#
47+# This program is distributed in the hope that it will be useful,
48+# but WITHOUT ANY WARRANTY; without even the implied warranty of
49+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
50+# GNU Affero General Public License for more details.
51+#
52+# You should have received a copy of the GNU Affero General Public License
53+# along with this program. If not, see <http://www.gnu.org/licenses/>.
54+#
55+###############################################################################
56+
57+
58+{
59+ 'name': 'SQL Export',
60+ 'version': '0.1',
61+ 'category': 'Generic Modules/Others',
62+ 'license': 'AGPL-3',
63+ 'description':
64+ """
65+ Allow to execute sql query from openerp interface in order to export datas in csv files.
66+ To edit or create a query, the user have to be in sql_query editor group.
67+ To execute a query, the user or group of user have to be specified in the sql_export record.
68+
69+ """,
70+ 'author': 'Akretion',
71+ 'website': 'http://www.akretion.com/',
72+ 'depends': [
73+ "base",
74+ ],
75+ 'init_xml': [],
76+ 'update_xml': [
77+ 'sql_view.xml',
78+ 'wizard/wizard_file_view.xml',
79+ 'security/sql_export_security.xml',
80+ 'security/ir.model.access.csv',
81+ ],
82+ 'demo_xml': [],
83+ 'installable': True,
84+}
85
86=== added directory 'sql_export/i18n'
87=== added file 'sql_export/i18n/fr.po'
88--- sql_export/i18n/fr.po 1970-01-01 00:00:00 +0000
89+++ sql_export/i18n/fr.po 2014-04-04 14:08:54 +0000
90@@ -0,0 +1,132 @@
91+# Translation of OpenERP Server.
92+# This file contains the translation of the following modules:
93+# * sql_export
94+#
95+msgid ""
96+msgstr ""
97+"Project-Id-Version: OpenERP Server 7.0\n"
98+"Report-Msgid-Bugs-To: \n"
99+"POT-Creation-Date: 2014-04-01 08:39+0000\n"
100+"PO-Revision-Date: 2014-04-01 08:39+0000\n"
101+"Last-Translator: <>\n"
102+"Language-Team: \n"
103+"MIME-Version: 1.0\n"
104+"Content-Type: text/plain; charset=UTF-8\n"
105+"Content-Transfer-Encoding: \n"
106+"Plural-Forms: \n"
107+
108+#. module: sql_export
109+#: model:ir.ui.menu,name:sql_export.menu_export
110+msgid "Export"
111+msgstr "Export"
112+
113+#. module: sql_export
114+#: code:addons/sql_export/sql.py:103
115+#: code:addons/sql_export/sql.py:114
116+#, python-format
117+msgid "Invalid Query"
118+msgstr "Requête Invalide"
119+
120+#. module: sql_export
121+#: model:res.groups,name:sql_export.group_sql_request_editor
122+msgid "Sql Request Editor"
123+msgstr "Edition de Requête SQL"
124+
125+#. module: sql_export
126+#: field:sql.export,name:0
127+msgid "Name"
128+msgstr "Nom"
129+
130+#. module: sql_export
131+#: model:ir.model,name:sql_export.model_sql_file_wizard
132+msgid "Allow to the user to save the file with sql request's data"
133+msgstr "Permet à l'utilisateur de sauvegarder le fichier contenant les données de la requête SQL"
134+
135+#. module: sql_export
136+#: code:addons/sql_export/sql.py:104
137+#: code:addons/sql_export/sql.py:115
138+#, python-format
139+msgid "The Sql query is not valid."
140+msgstr "La requête SQL n'est pas valide"
141+
142+#. module: sql_export
143+#: code:addons/sql_export/sql.py:72
144+#, python-format
145+msgid "No data"
146+msgstr "Pas de données"
147+
148+#. module: sql_export
149+#: model:ir.model,name:sql_export.model_sql_export
150+#: view:sql.export:0
151+msgid "SQL export"
152+msgstr "SQL export"
153+
154+#. module: sql_export
155+#: field:sql.file.wizard,file_name:0
156+msgid "File Name"
157+msgstr "Nom du fichier"
158+
159+#. module: sql_export
160+#: model:ir.actions.act_window,name:sql_export.sql_export_tree_action
161+#: view:sql.export:0
162+msgid "SQL Export"
163+msgstr "SQL Export"
164+
165+#. module: sql_export
166+#: field:sql.export,group_ids:0
167+msgid "Allowed Groups"
168+msgstr "Groupes Autorisés"
169+
170+#. module: sql_export
171+#: model:ir.ui.menu,name:sql_export.sql_export_menu
172+#: model:ir.ui.menu,name:sql_export.sql_export_menu_view
173+msgid "Sql Export"
174+msgstr "Sql Export"
175+
176+#. module: sql_export
177+#: constraint:sql.export:0
178+msgid "The query you want make is not allowed : prohibited actions (Delete, drop...)"
179+msgstr "La requête que vous voulez sauvegarder n'est pas autorisée : actions interdites (Delete, drop...)"
180+
181+#. module: sql_export
182+#: view:sql.file.wizard:0
183+msgid "Csv File"
184+msgstr "Fichier CSV"
185+
186+#. module: sql_export
187+#: view:sql.export:0
188+msgid "Execute Query"
189+msgstr "Exécuter"
190+
191+#. module: sql_export
192+#: field:sql.file.wizard,file:0
193+msgid "File"
194+msgstr "Fichier"
195+
196+#. module: sql_export
197+#: field:sql.export,query:0
198+msgid "Query"
199+msgstr "Query"
200+
201+#. module: sql_export
202+#: view:sql.export:0
203+msgid "Allowed Users Groups"
204+msgstr "Groupes d'utilisateurs Autorisés"
205+
206+#. module: sql_export
207+#: help:sql.export,query:0
208+msgid "You can't use the following word : delete, drop, create, insert, update, alter, truncate, execute"
209+msgstr "Vous ne pouvez pas utilisés les mots suivants : delete, drop, create, insert, update, alter, truncate, execute"
210+
211+#. module: sql_export
212+#: code:addons/sql_export/sql.py:73
213+#, python-format
214+msgid "The query did not return any data."
215+msgstr "La requête n'a retourné aucune donnée"
216+
217+#. module: sql_export
218+#: view:sql.export:0
219+#: field:sql.export,user_ids:0
220+msgid "Allowed Users"
221+msgstr "Utilisateurs Autorisés"
222+
223
224=== added directory 'sql_export/security'
225=== added file 'sql_export/security/ir.model.access.csv'
226--- sql_export/security/ir.model.access.csv 1970-01-01 00:00:00 +0000
227+++ sql_export/security/ir.model.access.csv 2014-04-04 14:08:54 +0000
228@@ -0,0 +1,3 @@
229+"id","name","model_id:id","group_id:id","perm_read","perm_write","perm_create","perm_unlink"
230+"access_sql_export_all","access_sql_export_all","model_sql_export",,1,0,0,0
231+"access_sql_export_editor","access_sql_export_editor","model_sql_export",group_sql_request_editor,1,1,1,1
232
233=== added file 'sql_export/security/sql_export_security.xml'
234--- sql_export/security/sql_export_security.xml 1970-01-01 00:00:00 +0000
235+++ sql_export/security/sql_export_security.xml 2014-04-04 14:08:54 +0000
236@@ -0,0 +1,21 @@
237+<?xml version="1.0" encoding="utf-8"?>
238+<openerp>
239+<data noupdate="0">
240+
241+ <record model="res.groups" id="group_sql_request_editor">
242+ <field name="name">Sql Request Editor</field>
243+ <field name="users" eval="[(4, ref('base.user_root'))]"/>
244+ </record>
245+
246+ <record model="ir.rule" id="sql_export_restric_access_user_or_group">
247+ <field name="name" >SQL Export users and groups rules</field>
248+ <field name="model_id" ref="model_sql_export"/>
249+ <field eval="1" name="perm_read"/>
250+ <field eval="0" name="perm_create"/>
251+ <field eval="0" name="perm_write"/>
252+ <field eval="0" name="perm_unlink"/>
253+ <field name="domain_force">['|', ('user_ids','=',user.id), ('group_ids','=', [x.id for x in user.groups_id])]</field>
254+ </record>
255+
256+</data>
257+</openerp>
258
259=== added file 'sql_export/sql.py'
260--- sql_export/sql.py 1970-01-01 00:00:00 +0000
261+++ sql_export/sql.py 2014-04-04 14:08:54 +0000
262@@ -0,0 +1,116 @@
263+# -*- coding: utf-8 -*-
264+###############################################################################
265+#
266+# action_server_email for OpenERP
267+# Copyright (C) 2013-TODAY Akretion <http://www.akretion.com>.
268+# @author Florian DA COSTA <florian.dacosta@akretion.com>
269+#
270+# This program is free software: you can redistribute it and/or modify
271+# it under the terms of the GNU Affero General Public License as
272+# published by the Free Software Foundation, either version 3 of the
273+# License, or (at your option) any later version.
274+#
275+# This program is distributed in the hope that it will be useful,
276+# but WITHOUT ANY WARRANTY; without even the implied warranty of
277+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
278+# GNU Affero General Public License for more details.
279+#
280+# You should have received a copy of the GNU Affero General Public License
281+# along with this program. If not, see <http://www.gnu.org/licenses/>.
282+#
283+###############################################################################
284+
285+from openerp.osv.orm import Model
286+from openerp.osv import fields, orm
287+from openerp.tools.translate import _
288+import StringIO
289+import csv
290+import base64
291+import time
292+
293+
294+PROHIBITED_WORDS = ['delete', 'drop', 'insert', 'alter', 'truncate', 'execute']
295+
296+
297+class SqlExport(Model):
298+
299+ _name = "sql.export"
300+
301+ _description = "SQL export"
302+
303+
304+ def _check_query(self, cr, uid, ids, context=None):
305+ for obj in self.browse(cr, uid, ids, context=context):
306+ if any(word in obj.query.lower() for word in PROHIBITED_WORDS):
307+ return False
308+ if obj.query.split(' ')[0].lower() != 'select':
309+ return False
310+ return True
311+
312+ def _get_editor_group(self, cr, uid, *args):
313+ gr_obj = self.pool.get('res.groups')
314+ editors = gr_obj.search(cr, uid, [('name', '=', 'Sql Request Editor')])
315+ return editors
316+
317+
318+ _columns = {
319+ 'name': fields.char('Name', required=True),
320+ 'query': fields.text('Query', required=True, help="You can't use the following word : delete, drop, create, insert, update, alter, truncate, execute"),
321+ 'group_ids': fields.many2many('res.groups', 'groups_sqlquery_rel', 'sql_id', 'group_id', 'Allowed Groups'),
322+ 'user_ids': fields.many2many('res.users', 'users_sqlquery_rel', 'sql_id', 'user_id', 'Allowed Users'),
323+ }
324+
325+
326+ _defaults = {
327+ 'group_ids' : _get_editor_group,
328+ }
329+
330+ _constraints = [(_check_query, 'The query you want make is not allowed : prohibited actions (Delete, drop...)', ['query'])]
331+
332+ def export_sql_query(self, cr, uid, ids, context=None):
333+ if not context:
334+ context = {}
335+ for obj in self.browse(cr, uid, ids, context=context):
336+ now = time.strftime('%Y-%m-%d',time.localtime())
337+ output = StringIO.StringIO()
338+ query = "COPY (" + obj.query + ") TO STDOUT WITH CSV HEADER DELIMITER ';'"
339+ cr.copy_expert(query, output)
340+ output.getvalue()
341+ new_output = base64.b64encode(output.getvalue())
342+ output.close()
343+ wiz = self.pool.get('sql.file.wizard').create(cr, uid, {'file': new_output, 'file_name': obj.name + '_' + now + '.csv'})
344+ cr.commit()
345+ return {
346+ 'view_type': 'form',
347+ 'view_mode': 'form',
348+ 'res_model': 'sql.file.wizard',
349+ 'res_id': wiz,
350+ 'type': 'ir.actions.act_window',
351+ 'target': 'new',
352+ 'context': context,
353+ 'nodestroy': True,
354+ }
355+
356+
357+ def write(self, cr, uid, ids, vals, context=None):
358+ if vals:
359+ if 'query' in vals:
360+ try:
361+ cr.execute(vals['query'])
362+ except:
363+ raise orm.except_orm(_("Invalid Query"),
364+ _("The Sql query is not valid."))
365+ return super(SqlExport, self).write(cr, uid, ids, vals, context=context)
366+
367+
368+ def create(self, cr, uid, vals, context=None):
369+ if vals:
370+ if 'query' in vals:
371+ try:
372+ cr.execute(vals['query'])
373+ except:
374+ raise orm.except_orm(_("Invalid Query"),
375+ _("The Sql query is not valid."))
376+ return super(SqlExport, self).create(cr, uid, vals, context=context)
377+
378+
379
380=== added file 'sql_export/sql_view.xml'
381--- sql_export/sql_view.xml 1970-01-01 00:00:00 +0000
382+++ sql_export/sql_view.xml 2014-04-04 14:08:54 +0000
383@@ -0,0 +1,63 @@
384+<?xml version="1.0" encoding="utf-8"?>
385+<openerp>
386+<data>
387+
388+<menuitem name="Export"
389+ id="menu_export"
390+ groups="base.group_user"
391+ sequence="190"/>
392+
393+
394+
395+
396+
397+
398+ <record id="sql_export_view_form" model="ir.ui.view">
399+ <field name="name">Sql_export_form_view</field>
400+ <field name="model">sql.export</field>
401+ <field name="arch" type="xml">
402+ <form string="SQL export">
403+ <group col="2">
404+ <group colspan="2" col="5">
405+ <label for="name" colspan="1"/>
406+ <field name="name" colspan="2" nolabel="1"/>
407+ <button name="export_sql_query" string="Execute Query" type="object" class="oe_highlight" icon="gtk-execute" colspan="2"/>
408+ <label for="query" colspan="1"/>
409+ <field name="query" nolabel="1" colspan="4"/>
410+ </group>
411+ <group colspan="2" col="2" groups="sql_export.group_sql_request_editor">
412+ <separator string="Allowed Users" colspan="1"/>
413+ <separator string="Allowed Users Groups" colspan="1"/>
414+ <field name="user_ids" nolabel="1"/>
415+ <field name="group_ids" nolabel="1"/>
416+ </group>
417+ </group>
418+ </form>
419+ </field>
420+ </record>
421+
422+ <record id="sql_export_view_tree" model="ir.ui.view">
423+ <field name="name">Sql_export_tree_view</field>
424+ <field name="model">sql.export</field>
425+ <field name="arch" type="xml">
426+ <tree string="SQL Export">
427+ <field name="name"/>
428+ </tree>
429+ </field>
430+ </record>
431+
432+ <record id="sql_export_tree_action" model="ir.actions.act_window">
433+ <field name="name">SQL Export</field>
434+ <field name="res_model">sql.export</field>
435+ <field name="view_type">form</field>
436+ <field name="view_mode">tree,form</field>
437+ </record>
438+
439+
440+ <menuitem id="sql_export_menu" name="Sql Export" parent="menu_export" sequence="1"/>
441+
442+ <menuitem id="sql_export_menu_view" name="Sql Export" parent="sql_export_menu" action="sql_export_tree_action" sequence="1"/>
443+
444+
445+</data>
446+</openerp>
447
448=== added directory 'sql_export/wizard'
449=== added file 'sql_export/wizard/__init__.py'
450--- sql_export/wizard/__init__.py 1970-01-01 00:00:00 +0000
451+++ sql_export/wizard/__init__.py 2014-04-04 14:08:54 +0000
452@@ -0,0 +1,23 @@
453+# -*- coding: utf-8 -*-
454+###############################################################################
455+#
456+# action_server_email for OpenERP
457+# Copyright (C) 2013-TODAY Akretion <http://www.akretion.com>.
458+# @author Florian DA COSTA <florian.dacosta@akretion.com>
459+#
460+# This program is free software: you can redistribute it and/or modify
461+# it under the terms of the GNU Affero General Public License as
462+# published by the Free Software Foundation, either version 3 of the
463+# License, or (at your option) any later version.
464+#
465+# This program is distributed in the hope that it will be useful,
466+# but WITHOUT ANY WARRANTY; without even the implied warranty of
467+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
468+# GNU Affero General Public License for more details.
469+#
470+# You should have received a copy of the GNU Affero General Public License
471+# along with this program. If not, see <http://www.gnu.org/licenses/>.
472+#
473+###############################################################################
474+
475+import wizard_file
476
477=== added file 'sql_export/wizard/wizard_file.py'
478--- sql_export/wizard/wizard_file.py 1970-01-01 00:00:00 +0000
479+++ sql_export/wizard/wizard_file.py 2014-04-04 14:08:54 +0000
480@@ -0,0 +1,41 @@
481+# -*- coding: utf-8 -*-
482+###############################################################################
483+#
484+# action_server_email for OpenERP
485+# Copyright (C) 2013-TODAY Akretion <http://www.akretion.com>.
486+# @author Florian DA COSTA <florian.dacosta@akretion.com>
487+#
488+# This program is free software: you can redistribute it and/or modify
489+# it under the terms of the GNU Affero General Public License as
490+# published by the Free Software Foundation, either version 3 of the
491+# License, or (at your option) any later version.
492+#
493+# This program is distributed in the hope that it will be useful,
494+# but WITHOUT ANY WARRANTY; without even the implied warranty of
495+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
496+# GNU Affero General Public License for more details.
497+#
498+# You should have received a copy of the GNU Affero General Public License
499+# along with this program. If not, see <http://www.gnu.org/licenses/>.
500+#
501+###############################################################################
502+
503+from openerp.osv.orm import Model
504+from openerp.osv import fields, orm
505+from openerp.tools.translate import _
506+
507+
508+class SqlFileWizard(orm.TransientModel):
509+
510+ _name = "sql.file.wizard"
511+
512+ _description = "Allow to the user to save the file with sql request's data"
513+
514+
515+ _columns = {
516+ 'file': fields.binary('File', required=True, readonly=True),
517+ 'file_name': fields.char('File Name', readonly=True),
518+ }
519+
520+
521+
522
523=== added file 'sql_export/wizard/wizard_file_view.xml'
524--- sql_export/wizard/wizard_file_view.xml 1970-01-01 00:00:00 +0000
525+++ sql_export/wizard/wizard_file_view.xml 2014-04-04 14:08:54 +0000
526@@ -0,0 +1,18 @@
527+<?xml version="1.0" encoding="utf-8"?>
528+<openerp>
529+ <data>
530+
531+ <record id="sql_file_wizard_view_form" model="ir.ui.view">
532+ <field name="name">sql.file.wizard.view.form</field>
533+ <field name="model">sql.file.wizard</field>
534+ <field name="arch" type="xml">
535+ <form string="Csv File">
536+ <field name="file" filename="file_name"/>
537+ <field name="file_name" invisible="1"/>
538+ </form>
539+ </field>
540+ </record>
541+
542+
543+ </data>
544+</openerp>