headers
Juan Manuel Clavero Almirón | 17 May 2012 10:19
Picon

Migrating free text 3.0 to dynamic fields 3.1

Hi all,

although OTRS 3.1 has been out for some time, I’ve started looking into it this week, in order to safe upgrade our systems.

Currently, we are using 2 freetext fields to store the current location of the customer: one for the building and the other for the location inside this building. Our buildings have names in our local language and there’s no point in translating them.

I’ve seen there is the script scripts/DBUpdate-to-3.1.pl, which moves the freetext stored in ticket table to dynamic field values. But it seems to me that it will only move the free text fields that OTRS uses (i.e. Decision Result and Due Date).

Am I right?

If so, Would it be difficult to grab the free text config in 3.0 and insert into dynamic fields in 3.1?

 

Kind regards,

Juan Clavero Almirón

 

_______________________________________________
OTRS mailing list: dev - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/dev
To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/dev
Permalink | Reply | 1 comment |
headers
Carlos Rodríguez | 17 May 2012 14:41
Gravatar

Re: Migrating free text 3.0 to dynamic fields 3.1

Hi Juan Manuel,

The DBUpdate-to-3.1.pl script is designed to take ALL the free fields from ticket and article and transform them into Dynamic Fields, so if the fields you have for Building and Location are standard otrs free fields (e.g. TicketFreeText2, TicketFreeText15, etc) they will be converted (including their values)... That's the main idea of this script ;-)

If the fields are not standard otrs free fields (I mean if you have customized the screens and add columns to the database to have something like TicketFreeText17 or TicketFreeText32) for sure the script will not migrate this kind of customizations.

Please notice that the configuration of the Dynamic Fields is different from the Free Fields, while the screen configuration (to display or hide Dynamic Fields per screen) is still in the sysconfig (and should be also migrated with the script), the Dynamic Field configuration is in a separated screen in the Admin Panel, with this new screen you can manage your fields, you can add or edit fields.

If your fields are defined as dropdown (a list of predefined values) you can choose in the field configuration if you want to translate the values or not, otherwise if the fields are defined just as input boxes (Dynamic Field type: Text) the values are not translated.

Also notice that if the script detects that an old free field is not in use it will mark it as disabled Dynamic Field, but the rest of migration process will be the same.

I hope this clarify your questions.

While we have been testing the upgrade process and the particularly the script , I still recommend to backup the database before the upgrade and/or first migrate a test system before touching the production instance.
   
((enjoy))

Carlos Rodríguez




On May 17, 2012, at 3:19 AM, Juan Manuel Clavero Almirón wrote:

Hi all,

although OTRS 3.1 has been out for some time, I’ve started looking into it this week, in order to safe upgrade our systems.

Currently, we are using 2 freetext fields to store the current location of the customer: one for the building and the other for the location inside this building. Our buildings have names in our local language and there’s no point in translating them.

I’ve seen there is the script scripts/DBUpdate-to-3.1.pl, which moves the freetext stored in ticket table to dynamic field values. But it seems to me that it will only move the free text fields that OTRS uses (i.e. Decision Result and Due Date).

Am I right?

If so, Would it be difficult to grab the free text config in 3.0 and insert into dynamic fields in 3.1?

 

Kind regards,

Juan Clavero Almirón

 

_______________________________________________
OTRS mailing list: dev - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/dev
To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/dev 

_______________________________________________
OTRS mailing list: dev - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/dev
To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/dev
Permalink | Reply | 0 comments |
headers
Michael Scheer | 25 May 2012 08:52
Picon

Localized and stripped return value of FetchrowArray?

Hi,

the following SQL query returns (ceate_time) e.g. "2012-05-22 11:26:39" 
- in the SQL query box for instance.

[Kernel/System/Ticket.pm in OTRS 3.1.5]
2535        return if !$Self->{DBObject}->Prepare(
2536            SQL => 'SELECT article_sender_type_id, article_type_id, 
create_time FROM '
2537                . 'article WHERE ticket_id = ? ORDER BY create_time 
ASC',
2538            Bind => [ \$Param{TicketID} ],
2539        );

BUT in the RowArray $Row[2] is "22.05.2012" - it's lacking the time and 
has german time format.

2433 while ( my  <at> Row = $Self->{DBObject}->FetchrowArray() ) {
             push  <at> SenderHistory, {
                 SenderTypeID  => $Row[0],
                 ArticleTypeID => $Row[1],
                 Created       => $Row[2],

Result is, other internal time functions get an invalid format as an 
input and return zero.

Therefore, in case of tickets, which haven't been manually touched by 
agents (tickets via mailin) escalation time is wrongly calculated. This 
applies for OTRS 3.1.5 still. I wanted to fix it, but could do this only 
with a rather dirty hack because of my FetchRowarray() problems.

TIA
Michael
_______________________________________________
OTRS mailing list: dev - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/dev
To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/dev
Permalink | Reply | 3 comments |
headers
Michiel Beijen | 25 May 2012 11:31
Gravatar

Re: Localized and stripped return value of FetchrowArray?

Hi Michael,

Michael Scheer schreef op 2012-05-25 08:52:

> the following SQL query returns (ceate_time) e.g. "2012-05-22 
> 11:26:39"
> - in the SQL query box for instance.

...

> BUT in the RowArray $Row[2] is "22.05.2012" - it's lacking the time 
> and
> has german time format.

Could it be that you use a database such as Oracle where you can 
influence the time stamp format based on user locale?
I think that the format for Apache (which you'll see via the web front 
end) and the otrs user (which your emails will use) will return a 
different time stamp format.

In case you're using Oracle, you might want to stick this line into 
your Config.pm : $ENV{NLS_DATE_FORMAT} = 'YYYY-MM-DD HH24:MI:SS';
--
Mike
_______________________________________________
OTRS mailing list: dev - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/dev
To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/dev
Permalink | Reply | 2 comments |
headers
Stefano Boccanera | 25 May 2012 15:07
Picon

: How to find the link btw ticket and dynamic_field value ?

Hi
I migrated from otrs 3.0.9 to 3.1.5.
All sounds fine, after I migrated the DB (postgres 8.4), but I didn't find how the ticket are linked to the dynamic fields created.

This information are vital for me, because I have many automated reports using specific sql select.

So before to migrate the production installation I have to modify any
select to output the right informations.

I just saw to the OTRSDatabaseDiagram.png, but no link relation is depicted btw ticket and dynamic_field or dynamic_field_value tables.

I suspect, after a very brief cross analysis, that object_id column into dynamic_field_value table contains the ticket id reference, but an extensive check is a very  time consuming job.

So anyone can help me on this information?

best regards

--

 
Stefano Boccanera 

_______________________________________________
OTRS mailing list: dev - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/dev
To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/dev
Permalink | Reply | 0 comments |
headers
Aaron G. Angeles | 25 May 2012 16:19
Picon
Favicon

Upgrading OTRS from 3.0.9 to 3.1.5


Hi Guys,

Can anyone help me convert the following code for MSSQL?

ALTER IGNORE TABLE ticket_flag ADD CONSTRAINT ticket_flag_per_user UNIQUE INDEX (ticket_id, ticket_key, create_by);

Thanks in advance!
Aaron
_______________________________________________
OTRS mailing list: dev - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/dev
To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/dev
Permalink | Reply | 1 comment |
headers
Michiel Beijen | 25 May 2012 16:37
Gravatar

Re: Upgrading OTRS from 3.0.9 to 3.1.5

Aaron G. Angeles schreef op 2012-05-25 16:19:

> Can anyone help me convert the following code for MSSQL?
>
> ALTER IGNORE TABLE ticket_flag ADD CONSTRAINT ticket_flag_per_user
> UNIQUE INDEX (ticket_id, ticket_key, create_by);

Hi Aaron; you should not have to!
Check http://bugs.otrs.org/show_bug.cgi?id=8237#c9
"Error while applying ticket_flags constraint when running upgrade from 
3.0 to 3.1"

--
Mike
_______________________________________________
OTRS mailing list: dev - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/dev
To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/dev
Permalink | Reply | 0 comments |
headers
Juan Manuel Clavero Almirón | 29 May 2012 16:48
Picon

Re: Migrating free text 3.0 to dynamic fields 3.1

Hi all,

I'm afraid I asked after reading the code, but before really trying it.
My first attempt was on a freshly installed test-environment OTRS-3.1.5. I dumped my dev-OTRS-3.0 ticket
data in this 3.1-dev and THEN made the upgrade as explained in UPGRADING, step 8. And it didn't go well, of course.
Now I've duplicated my dev-OTRS-3.0 and upgraded it to OTRS-3.1.5, as explained, and it worked
seamlessly: all my freetext are there, even the 200 options of one of them, and they are visible in the same
windows as they were before.
So, here's an apology for asking beforehand and a gratitude to all OTRS developers for such a well-done task

Juan Clavero

----------------------------------------------------------------------
|
| Date: Thu, 17 May 2012 07:41:49 -0500
| From: Carlos Rodr?guez <carlos.rodriguez <at> otrs.com>
| Subject: Re: [dev] Migrating free text 3.0 to dynamic fields 3.1
| To: Development community of OTRS <dev <at> otrs.org>
| Message-ID: <5D57F2C4-25CA-4FB6-A68E-5C448E6E7417 <at> otrs.com>
| Content-Type: text/plain; charset="windows-1252"
| 
| Hi Juan Manuel,
| 
| The DBUpdate-to-3.1.pl script is designed to take ALL the free fields from ticket and article and
transform them into Dynamic Fields, so if the fields you have for Building and Location are standard otrs
free fields (e.g. TicketFreeText2, TicketFreeText15, etc) they will be converted (including their
values)... That's the main idea of this script ;-)
| 
| If the fields are not standard otrs free fields (I mean if you have customized the screens and add columns to
the database to have something like TicketFreeText17 or TicketFreeText32) for sure the script will not
migrate this kind of customizations.
| 
| Please notice that the configuration of the Dynamic Fields is different from the Free Fields, while the
screen configuration (to display or hide Dynamic Fields per screen) is still in the sysconfig (and should
be also migrated with the script), the Dynamic Field configuration is in a separated screen in the Admin
Panel, with this new screen you can manage your fields, you can add or edit fields.
| 
| If your fields are defined as dropdown (a list of predefined values) you can choose in the field
configuration if you want to translate the values or not, otherwise if the fields are defined just as input
boxes (Dynamic Field type: Text) the values are not translated.
| 
| Also notice that if the script detects that an old free field is not in use it will mark it as disabled Dynamic
Field, but the rest of migration process will be the same.
| 
| I hope this clarify your questions.
| 
| While we have been testing the upgrade process and the particularly the script , I still recommend to
backup the database before the upgrade and/or first migrate a test system before touching the production instance.
|    
| ((enjoy))
| 
| Carlos Rodr?guez
| 
| 
| 
| 
| On May 17, 2012, at 3:19 AM, Juan Manuel Clavero Almir?n wrote:
| 
| > Hi all,
| > although OTRS 3.1 has been out for some time, I?ve started looking into it this week, in order to safe
upgrade our systems.
| > Currently, we are using 2 freetext fields to store the current location of the customer: one for the
building and the other for the location inside this building. Our buildings have names in our local
language and there?s no point in translating them.
| > I?ve seen there is the script scripts/DBUpdate-to-3.1.pl, which moves the freetext stored in ticket
table to dynamic field values. But it seems to me that it will only move the free text fields that OTRS uses
(i.e. Decision Result and Due Date).
| > Am I right?
| > If so, Would it be difficult to grab the free text config in 3.0 and insert into dynamic fields in 3.1?
| >  
| > Kind regards,
| > Juan Clavero Almir?n
| >  
| > _______________________________________________
| > OTRS mailing list: dev - Webpage: http://otrs.org/
| > Archive: http://lists.otrs.org/pipermail/dev
| > To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/dev
| 
_______________________________________________
OTRS mailing list: dev - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/dev
To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/dev
Permalink | Reply | 1 comment |
headers
Juan Manuel Clavero Almirón | 29 May 2012 16:51
Picon

Re: How to find the link btw ticket and dynamic_field value ?

Hi Stefano,
I think the info you are looking for is in the dynamic_field_value table.
The object_id field may be an article or a ticket, depending on the dynamic_field pointed in the dynamic_id field
Hope it helps

Kind regards,
Juan Clavero Almirón

----------------------------------------------------------------------
| Date: Fri, 25 May 2012 15:07:29 +0200
| From: Stefano Boccanera <sboccanera <at> gmail.com>
| Subject: [dev] : How to find the link btw ticket and dynamic_field
| 	value ?
| To: developer list OTRS <dev <at> otrs.org>
| Message-ID:
| 	<CAHMQiK5nQcN10_8+vkH=jNqp_E0k1q849fLcAJS3o4cUnj3z+g <at> mail.gmail.com>
| Content-Type: text/plain; charset="utf-8"
| 
| Hi
| I migrated from otrs 3.0.9 to 3.1.5.
| All sounds fine, after I migrated the DB (postgres 8.4), but I didn't find how the ticket are linked to the
dynamic fields created.
| 
| This information are vital for me, because I have many automated reports using specific sql select.
| 
| So before to migrate the production installation I have to modify any select to output the right informations.
| 
| I just saw to the OTRSDatabaseDiagram.png, but no link relation is depicted btw ticket and dynamic_field
or dynamic_field_value tables.
| 
| I suspect, after a very brief cross analysis, that object_id column into dynamic_field_value table
contains the ticket id reference, but an extensive check is a very  time consuming job.
| 
| So anyone can help me on this information?
| 
| best regards
| 
| --
|  *
| Stefano Boccanera*
|
_______________________________________________
OTRS mailing list: dev - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/dev
To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/dev
Permalink | Reply | 1 comment |
headers
Carlos Rodríguez | 29 May 2012 17:01
Gravatar

Re: How to find the link btw ticket and dynamic_field value ?

Hi Stefano, Juan

I totally agree with Juan.

((enjoy))

Carlos Rodríguez




On May 29, 2012, at 9:51 AM, Juan Manuel Clavero Almirón wrote:

Hi Stefano,
I think the info you are looking for is in the dynamic_field_value table.
The object_id field may be an article or a ticket, depending on the dynamic_field pointed in the dynamic_id field
Hope it helps

Kind regards,
Juan Clavero Almirón

----------------------------------------------------------------------
| Date: Fri, 25 May 2012 15:07:29 +0200
| From: Stefano Boccanera <sboccanera <at> gmail.com>
| Subject: [dev] : How to find the link btw ticket and dynamic_field
| value ?
| To: developer list OTRS <dev <at> otrs.org>
| Message-ID:
| <CAHMQiK5nQcN10_8+vkH=jNqp_E0k1q849fLcAJS3o4cUnj3z+g <at> mail.gmail.com>
| Content-Type: text/plain; charset="utf-8"
|
| Hi
| I migrated from otrs 3.0.9 to 3.1.5.
| All sounds fine, after I migrated the DB (postgres 8.4), but I didn't find how the ticket are linked to the dynamic fields created.
|
| This information are vital for me, because I have many automated reports using specific sql select.
|
| So before to migrate the production installation I have to modify any select to output the right informations.
|
| I just saw to the OTRSDatabaseDiagram.png, but no link relation is depicted btw ticket and dynamic_field or dynamic_field_value tables.
|
| I suspect, after a very brief cross analysis, that object_id column into dynamic_field_value table contains the ticket id reference, but an extensive check is a very  time consuming job.
|
| So anyone can help me on this information?
|
| best regards
|
| --
|  *
| Stefano Boccanera*
|
_______________________________________________
OTRS mailing list: dev - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/dev
To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/dev 

_______________________________________________
OTRS mailing list: dev - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/dev
To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/dev
Permalink | Reply | 0 comments |

Gmane