Re: export with SQL querie(s)

Thanks Susan.
Best,
S.

> Le 18 déc. 2016 à 11:21, Susan Dittmar <[email protected]> a écrit :
> 
> Hi Stéphane,
> 
> as far as I know MySQL can export CSV tables, but only on a
> per-table-basis (which is fine, as CSV cannot handle multiple tables).
> So first you need a list of all tables. The SQL commands to get that is:
> 
> use otrs;
> show tables;
> 
> In case of OTRS 5s this list is:
> 
> +------------------------------+
> | Tables_in_otrs               |
> +------------------------------+
> | acl                          |
> | acl_sync                     |
> | article                      |
> | article_attachment           |
> | article_flag                 |
> | article_plain                |
> | article_search               |
> | article_sender_type          |
> | article_type                 |
> | auto_response                |
> | auto_response_type           |
> | cloud_service_config         |
> | customer_company             |
> | customer_preferences         |
> | customer_user                |
> | dynamic_field                |
> | dynamic_field_value          |
> | follow_up_possible           |
> | generic_agent_jobs           |
> | gi_debugger_entry            |
> | gi_debugger_entry_content    |
> | gi_object_lock_state         |
> | gi_webservice_config         |
> | gi_webservice_config_history |
> | group_customer_user          |
> | group_role                   |
> | group_user                   |
> | groups                       |
> | link_object                  |
> | link_relation                |
> | link_state                   |
> | link_type                    |
> | mail_account                 |
> | notification_event           |
> | notification_event_item      |
> | notification_event_message   |
> | package_repository           |
> | personal_queues              |
> | personal_services            |
> | pm_activity                  |
> | pm_activity_dialog           |
> | pm_entity_sync               |
> | pm_process                   |
> | pm_transition                |
> | pm_transition_action         |
> | postmaster_filter            |
> | process_id                   |
> | queue                        |
> | queue_auto_response          |
> | queue_preferences            |
> | queue_standard_template      |
> | role_user                    |
> | roles                        |
> | salutation                   |
> | scheduler_future_task        |
> | scheduler_recurrent_task     |
> | scheduler_task               |
> | search_profile               |
> | service                      |
> | service_customer_user        |
> | service_preferences          |
> | service_sla                  |
> | sessions                     |
> | signature                    |
> | sla                          |
> | sla_preferences              |
> | smime_signer_cert_relations  |
> | standard_attachment          |
> | standard_template            |
> | standard_template_attachment |
> | system_address               |
> | system_data                  |
> | system_maintenance           |
> | ticket                       |
> | ticket_flag                  |
> | ticket_history               |
> | ticket_history_type          |
> | ticket_index                 |
> | ticket_lock_index            |
> | ticket_lock_type             |
> | ticket_loop_protection       |
> | ticket_priority              |
> | ticket_state                 |
> | ticket_state_type            |
> | ticket_type                  |
> | ticket_watcher               |
> | time_accounting              |
> | user_preferences             |
> | users                        |
> | valid                        |
> | virtual_fs                   |
> | virtual_fs_db                |
> | virtual_fs_preferences       |
> | web_upload_cache             |
> | xml_storage                  |
> +------------------------------+
> 
> Most of those tables you will only need to export when you change
> general settings.
> 
> To export one table as CSV you can either use MySQL directly with
> something like
> 
> SELECT *
> FROM put_in_table_name_here
> INTO OUTFILE 'put_in_full_path_to_file_here'
> FIELDS TERMINATED BY ';'
> OPTIONALLY ENCLOSED BY '"'
> ESCAPED BY '\\'
> LINES TERMINATED BY '\n';
> 
> Or you can use OTRS's SQL box with
> 
> Query: SELECT * FROM put_in_table_name_here
> Limit: something bigger than expected number of rows
> format: CSV
> 
> In both cases the SELECT statement can be more complex and can contain
> columns from several tables. Syntax is something like that:
> 
> SELECT list_of_columns_separated_by_comma
> FROM list_of_tables_separated_by_comma
> WHERE list_of_restrictions_as_boolean_expression
> 
> In this case you need to decide which columns you need and how to
> combine those tables (which means how to decide which row(s) of the
> first table to combine with which row(s) of the second table). I
> wouldn't want to try to re-combine the complete ticket structure.
> There's good reason why it has been split into several tables by the
> developers. It is possible though.
> 
> Now it's up to you to decide what exactly you need and to put it together.
> 
> Susan
> 
> ---------------------------------------------------------------------
> OTRS mailing list: otrs - Webpage: http://otrs.org/
> Archive: http://lists.otrs.org/pipermail/otrs
> To unsubscribe: http://lists.otrs.org/mailman/listinfo/otrs

--
Stéphane POUYLLAU
Ingénieur de recherche CNRS
Directeur technique d'Huma-Num

http://www.huma-num.fr
http://humanum.hypotheses.org

Twitter : http://twitter.com/spouyllau
Blog perso : http://blog.stephanepouyllau.org

---------------------------------------------------------------------
OTRS mailing list: otrs - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/otrs
To unsubscribe: http://lists.otrs.org/mailman/listinfo/otrs