<?xml version="1.0" encoding="utf-8" ?>

<database Name="otrs">

    <TableAlter NameOld="system_user" NameNew="users"/>

    <TableCreate Name="queue_preferences">
        <Column Name="queue_id" Required="true" Type="INTEGER"/>
        <Column Name="preferences_key" Required="true" Size="150" Type="VARCHAR"/>
        <Column Name="preferences_value" Required="false" Size="250" Type="VARCHAR"/>
        <Index Name="queue_preferences_queue_id">
            <IndexColumn Name="queue_id"/>
        </Index>
        <ForeignKey ForeignTable="queue">
            <Reference Local="queue_id" Foreign="id"/>
        </ForeignKey>
    </TableCreate>

    <TableCreate Name="service_sla">
        <Column Name="service_id" Required="true" Type="INTEGER"/>
        <Column Name="sla_id" Required="true" Type="INTEGER"/>
        <Unique Name="service_sla_service_sla">
            <UniqueColumn Name="service_id"/>
            <UniqueColumn Name="sla_id"/>
        </Unique>
        <ForeignKey ForeignTable="service">
            <Reference Local="service_id" Foreign="id"/>
        </ForeignKey>
        <ForeignKey ForeignTable="sla">
            <Reference Local="sla_id" Foreign="id"/>
        </ForeignKey>
    </TableCreate>

    <TableCreate Name="link_type">
        <Column Name="id" Required="true" PrimaryKey="true" AutoIncrement="true" Type="SMALLINT"/>
        <Column Name="name" Required="true" Size="50" Type="VARCHAR"/>
        <Column Name="valid_id" Required="true" Type="SMALLINT"/>
        <Column Name="create_time" Required="true" Type="DATE"/>
        <Column Name="create_by" Required="true" Type="INTEGER"/>
        <Column Name="change_time" Required="true" Type="DATE"/>
        <Column Name="change_by" Required="true" Type="INTEGER"/>
        <Unique Name="link_type_name">
            <UniqueColumn Name="name"/>
        </Unique>
        <ForeignKey ForeignTable="valid">
            <Reference Local="valid_id" Foreign="id"/>
        </ForeignKey>
        <ForeignKey ForeignTable="users">
            <Reference Local="create_by" Foreign="id"/>
            <Reference Local="change_by" Foreign="id"/>
        </ForeignKey>
    </TableCreate>

    <TableCreate Name="link_state">
        <Column Name="id" Required="true" PrimaryKey="true" AutoIncrement="true" Type="SMALLINT"/>
        <Column Name="name" Required="true" Size="50" Type="VARCHAR"/>
        <Column Name="valid_id" Required="true" Type="SMALLINT"/>
        <Column Name="create_time" Required="true" Type="DATE"/>
        <Column Name="create_by" Required="true" Type="INTEGER"/>
        <Column Name="change_time" Required="true" Type="DATE"/>
        <Column Name="change_by" Required="true" Type="INTEGER"/>
        <Unique Name="link_state_name">
            <UniqueColumn Name="name"/>
        </Unique>
        <ForeignKey ForeignTable="valid">
            <Reference Local="valid_id" Foreign="id"/>
        </ForeignKey>
        <ForeignKey ForeignTable="users">
            <Reference Local="create_by" Foreign="id"/>
            <Reference Local="change_by" Foreign="id"/>
        </ForeignKey>
    </TableCreate>

    <TableCreate Name="link_object">
        <Column Name="id" Required="true" PrimaryKey="true" AutoIncrement="true" Type="SMALLINT"/>
        <Column Name="name" Required="true" Size="100" Type="VARCHAR"/>
        <Unique Name="link_object_name">
            <UniqueColumn Name="name"/>
        </Unique>
    </TableCreate>

    <TableCreate Name="link_relation">
        <Column Name="source_object_id" Required="true" Type="SMALLINT"/>
        <Column Name="source_key" Required="true" Size="50" Type="VARCHAR"/>
        <Column Name="target_object_id" Required="true" Type="SMALLINT"/>
        <Column Name="target_key" Required="true" Size="50" Type="VARCHAR"/>
        <Column Name="type_id" Required="true" Type="SMALLINT"/>
        <Column Name="state_id" Required="true" Type="SMALLINT"/>
        <Column Name="create_time" Required="true" Type="DATE"/>
        <Column Name="create_by" Required="true" Type="INTEGER"/>
        <Unique Name="link_relation_view">
            <UniqueColumn Name="source_object_id"/>
            <UniqueColumn Name="source_key"/>
            <UniqueColumn Name="target_object_id"/>
            <UniqueColumn Name="target_key"/>
            <UniqueColumn Name="type_id"/>
        </Unique>
        <ForeignKey ForeignTable="link_object">
            <Reference Local="source_object_id" Foreign="id"/>
            <Reference Local="target_object_id" Foreign="id"/>
        </ForeignKey>
        <ForeignKey ForeignTable="link_type">
            <Reference Local="type_id" Foreign="id"/>
        </ForeignKey>
        <ForeignKey ForeignTable="link_state">
            <Reference Local="state_id" Foreign="id"/>
        </ForeignKey>
        <ForeignKey ForeignTable="users">
            <Reference Local="create_by" Foreign="id"/>
        </ForeignKey>
    </TableCreate>

    <TableAlter Name="user_preferences">
        <IndexCreate Name="user_preferences_user_id">
            <IndexColumn Name="user_id"/>
        </IndexCreate>
    </TableAlter>

    <TableAlter Name="group_user">
        <IndexCreate Name="group_user_user_id">
            <IndexColumn Name="user_id"/>
        </IndexCreate>
        <IndexCreate Name="group_user_group_id">
            <IndexColumn Name="group_id"/>
        </IndexCreate>
    </TableAlter>

    <TableAlter Name="group_role">
        <IndexCreate Name="group_role_role_id">
            <IndexColumn Name="role_id"/>
        </IndexCreate>
        <IndexCreate Name="group_role_group_id">
            <IndexColumn Name="group_id"/>
        </IndexCreate>
    </TableAlter>

    <TableAlter Name="group_customer_user">
        <IndexCreate Name="group_customer_user_user_id">
            <IndexColumn Name="user_id"/>
        </IndexCreate>
        <IndexCreate Name="group_customer_user_group_id">
            <IndexColumn Name="group_id"/>
        </IndexCreate>
    </TableAlter>

    <TableAlter Name="role_user">
        <IndexCreate Name="role_user_user_id">
            <IndexColumn Name="user_id"/>
        </IndexCreate>
        <IndexCreate Name="role_user_role_id">
            <IndexColumn Name="role_id"/>
        </IndexCreate>
    </TableAlter>

    <TableAlter Name="personal_queues">
        <IndexCreate Name="personal_queues_user_id">
            <IndexColumn Name="user_id"/>
        </IndexCreate>
        <IndexCreate Name="personal_queues_queue_id">
            <IndexColumn Name="queue_id"/>
        </IndexCreate>
    </TableAlter>

    <TableAlter Name="queue">
        <ColumnAdd Name="first_response_notify" Required="false" Type="SMALLINT"/>
        <ColumnAdd Name="update_notify" Required="false" Type="SMALLINT"/>
        <ColumnAdd Name="solution_notify" Required="false" Type="SMALLINT"/>
        <IndexCreate Name="queue_group_id">
            <IndexColumn Name="group_id"/>
        </IndexCreate>
    </TableAlter>

    <TableAlter Name="ticket">
        <ColumnChange NameOld="escalation_start_time" NameNew="escalation_update_time" Required="true" Type="INTEGER"/>
        <ColumnAdd Name="escalation_time" Required="true" Type="INTEGER" Default="0"/>
        <IndexCreate Name="ticket_escalation_time">
            <IndexColumn Name="escalation_time"/>
        </IndexCreate>
        <IndexCreate Name="ticket_escalation_update_time">
            <IndexColumn Name="escalation_update_time"/>
        </IndexCreate>
        <IndexCreate Name="ticket_escalation_response_time">
            <IndexColumn Name="escalation_response_time"/>
        </IndexCreate>
        <IndexCreate Name="ticket_escalation_solution_time">
            <IndexColumn Name="escalation_solution_time"/>
        </IndexCreate>
        <IndexCreate Name="ticket_title">
            <IndexColumn Name="title"/>
        </IndexCreate>
        <IndexCreate Name="ticket_customer_user_id">
            <IndexColumn Name="customer_user_id"/>
        </IndexCreate>
        <IndexCreate Name="ticket_customer_id">
            <IndexColumn Name="customer_id"/>
        </IndexCreate>
        <IndexCreate Name="ticket_queue_id">
            <IndexColumn Name="queue_id"/>
        </IndexCreate>
        <IndexCreate Name="ticket_ticket_lock_id">
            <IndexColumn Name="ticket_lock_id"/>
        </IndexCreate>
        <IndexCreate Name="ticket_responsible_user_id">
            <IndexColumn Name="responsible_user_id"/>
        </IndexCreate>
        <IndexCreate Name="ticket_ticket_state_id">
            <IndexColumn Name="ticket_state_id"/>
        </IndexCreate>
        <IndexCreate Name="ticket_ticket_priority_id">
            <IndexColumn Name="ticket_priority_id"/>
        </IndexCreate>
    </TableAlter>

    <TableAlter Name="object_link">
        <IndexCreate Name="index_object_link_a_id">
            <IndexColumn Name="object_link_a_id"/>
        </IndexCreate>
        <IndexCreate Name="index_object_link_b_id">
            <IndexColumn Name="object_link_b_id"/>
        </IndexCreate>
        <IndexCreate Name="index_object_link_a_object">
            <IndexColumn Name="object_link_a_object"/>
        </IndexCreate>
        <IndexCreate Name="index_object_link_b_object">
            <IndexColumn Name="object_link_b_object"/>
        </IndexCreate>
        <IndexCreate Name="index_object_link_type">
            <IndexColumn Name="object_link_type"/>
        </IndexCreate>
    </TableAlter>

    <TableAlter Name="ticket_history">
        <IndexCreate Name="ticket_history_history_type_id">
            <IndexColumn Name="history_type_id"/>
        </IndexCreate>
        <IndexCreate Name="ticket_history_queue_id">
            <IndexColumn Name="queue_id"/>
        </IndexCreate>
        <IndexCreate Name="ticket_history_type_id">
            <IndexColumn Name="type_id"/>
        </IndexCreate>
        <IndexCreate Name="ticket_history_owner_id">
            <IndexColumn Name="owner_id"/>
        </IndexCreate>
        <IndexCreate Name="ticket_history_priority_id">
            <IndexColumn Name="priority_id"/>
        </IndexCreate>
        <IndexCreate Name="ticket_history_state_id">
            <IndexColumn Name="state_id"/>
        </IndexCreate>
    </TableAlter>

    <TableAlter Name="sla">
        <ColumnAdd Name="first_response_notify" Required="false" Type="SMALLINT"/>
        <ColumnAdd Name="update_notify" Required="false" Type="SMALLINT"/>
        <ColumnAdd Name="solution_notify" Required="false" Type="SMALLINT"/>
        <ForeignKeyDrop ForeignTable="service">
            <Reference Local="service_id" Foreign="id"/>
        </ForeignKeyDrop>
    </TableAlter>

    <TableAlter Name="article">
        <IndexCreate Name="article_article_type_id">
            <IndexColumn Name="article_type_id"/>
        </IndexCreate>
        <IndexCreate Name="article_article_sender_type_id">
            <IndexColumn Name="article_sender_type_id"/>
        </IndexCreate>
    </TableAlter>

    <TableCreate Name="article_search">
        <Column Name="id" Required="true" PrimaryKey="true" AutoIncrement="true" Type="BIGINT"/>
        <Column Name="ticket_id" Required="true" Type="BIGINT"/>
        <Column Name="article_type_id" Required="true" Type="SMALLINT"/>
        <Column Name="article_sender_type_id" Required="true" Type="SMALLINT"/>
        <Column Name="a_from" Required="false" Size="3800" Type="VARCHAR"/>
        <Column Name="a_to" Required="false" Size="3800" Type="VARCHAR"/>
        <Column Name="a_cc" Required="false" Size="3800" Type="VARCHAR"/>
        <Column Name="a_subject" Required="false" Size="3800" Type="VARCHAR"/>
        <Column Name="a_message_id" Required="false" Size="3800" Type="VARCHAR"/>
        <Column Name="a_body" Required="true" Size="1800000" Type="VARCHAR"/>
        <Column Name="incoming_time" Required="true" Type="INTEGER"/>
        <Column Name="a_freekey1" Required="false" Size="250" Type="VARCHAR"/>
        <Column Name="a_freetext1" Required="false" Size="250" Type="VARCHAR"/>
        <Column Name="a_freekey2" Required="false" Size="250" Type="VARCHAR"/>
        <Column Name="a_freetext2" Required="false" Size="250" Type="VARCHAR"/>
        <Column Name="a_freekey3" Required="false" Size="250" Type="VARCHAR"/>
        <Column Name="a_freetext3" Required="false" Size="250" Type="VARCHAR"/>
        <Index Name="article_search_ticket_id">
            <IndexColumn Name="ticket_id"/>
        </Index>
        <Index Name="article_search_article_type_id">
            <IndexColumn Name="article_type_id"/>
        </Index>
        <Index Name="article_search_article_sender_type_id">
            <IndexColumn Name="article_sender_type_id"/>
        </Index>
        <Index Name="article_search_message_id">
            <IndexColumn Name="a_message_id" Size="255"/>
        </Index>
        <ForeignKey ForeignTable="ticket">
            <Reference Local="ticket_id" Foreign="id"/>
        </ForeignKey>
        <ForeignKey ForeignTable="article_type">
            <Reference Local="article_type_id" Foreign="id"/>
        </ForeignKey>
        <ForeignKey ForeignTable="article_sender_type">
            <Reference Local="article_sender_type_id" Foreign="id"/>
        </ForeignKey>
    </TableCreate>

    <TableAlter Name="ticket_watcher">
        <IndexCreate Name="ticket_watcher_user_id">
            <IndexColumn Name="user_id"/>
        </IndexCreate>
        <ForeignKeyCreate ForeignTable="ticket">
            <Reference Local="ticket_id" Foreign="id"/>
        </ForeignKeyCreate>
        <ForeignKeyCreate ForeignTable="users">
            <Reference Local="user_id" Foreign="id"/>
            <Reference Local="create_by" Foreign="id"/>
            <Reference Local="change_by" Foreign="id"/>
        </ForeignKeyCreate>
    </TableAlter>

    <TableAlter Name="ticket_index">
        <IndexCreate Name="ticket_index_queue_id">
            <IndexColumn Name="queue_id"/>
        </IndexCreate>
        <IndexCreate Name="ticket_index_group_id">
            <IndexColumn Name="group_id"/>
        </IndexCreate>
        <ForeignKeyCreate ForeignTable="ticket">
            <Reference Local="ticket_id" Foreign="id"/>
        </ForeignKeyCreate>
        <ForeignKeyCreate ForeignTable="queue">
            <Reference Local="queue_id" Foreign="id"/>
        </ForeignKeyCreate>
        <ForeignKeyCreate ForeignTable="groups">
            <Reference Local="group_id" Foreign="id"/>
        </ForeignKeyCreate>
    </TableAlter>

    <TableAlter Name="postmaster_filter">
        <IndexCreate Name="postmaster_filter_f_name">
            <IndexColumn Name="f_name"/>
        </IndexCreate>
    </TableAlter>

    <TableAlter Name="generic_agent_jobs">
        <IndexCreate Name="generic_agent_jobs_job_name">
            <IndexColumn Name="job_name"/>
        </IndexCreate>
    </TableAlter>

    <TableAlter NameNew="mail_account" NameOld="pop3_account">
        <ColumnAdd Name="account_type" Required="false" Size="20" Type="VARCHAR"/>
    </TableAlter>

    <TableAlter Name="article">
        <ColumnChange NameOld="a_body" NameNew="a_body" Required="true" Size="1800000" Type="VARCHAR"/>
    </TableAlter>

    <TableAlter Name="xml_storage">
        <ColumnChange NameOld="xml_content_value" NameNew="xml_content_value" Required="false" Size="1000000" Type="VARCHAR"/>
    </TableAlter>

    <Insert Table="notifications">
        <Data Key="notification_type" Type="Quote">Agent::EscalationNotifyBefore</Data>
        <Data Key="notification_charset" Type="Quote">iso-8859-1</Data>
        <Data Key="notification_language" Type="Quote">en</Data>
        <Data Key="subject" Type="Quote">Ticket Escalation Warning! (&lt;OTRS_CUSTOMER_SUBJECT[24]&gt;)</Data>
        <Data Key="text" Type="Quote">Hi &lt;OTRS_UserFirstname&gt;,

the ticket "&lt;OTRS_TICKET_TicketNumber&gt;" will escalate!

Escalation at: &lt;OTRS_TICKET_EscalationDestinationDate&gt;
Escalation in: &lt;OTRS_TICKET_EscalationDestinationIn&gt;

&lt;OTRS_CUSTOMER_FROM&gt;

wrote:
&lt;snip&gt;
&lt;OTRS_CUSTOMER_EMAIL[30]&gt;
&lt;snip&gt;

Please have a look at:

&lt;OTRS_CONFIG_HttpType&gt;://&lt;OTRS_CONFIG_FQDN&gt;/&lt;OTRS_CONFIG_ScriptAlias&gt;index.pl?Action=AgentZoom&amp;TicketID=&lt;OTRS_TICKET_TicketID&gt;

Your OTRS Notification Master</Data>
        <Data Key="create_by">1</Data>
        <Data Key="create_time">current_timestamp</Data>
        <Data Key="change_by">1</Data>
        <Data Key="change_time">current_timestamp</Data>
    </Insert>

    <Insert Table="notifications">
        <Data Key="notification_type" Type="Quote">Agent::EscalationNotifyBefore</Data>
        <Data Key="notification_charset" Type="Quote">iso-8859-1</Data>
        <Data Key="notification_language" Type="Quote">de</Data>
        <Data Key="subject" Type="Quote">Ticket Eskalations-Warnung! (&lt;OTRS_CUSTOMER_SUBJECT[24]&gt;)</Data>
        <Data Key="text" Type="Quote">Hallo &lt;OTRS_UserFirstname&gt; &lt;OTRS_UserLastname&gt;,

das Ticket "&lt;OTRS_TICKET_TicketNumber&gt;" wird bald eskalieren!

Eskalation um: &lt;OTRS_TICKET_EscalationDestinationDate&gt;
Eskalation in: &lt;OTRS_TICKET_EscalationDestinationIn&gt;

&lt;OTRS_CUSTOMER_FROM&gt;

schrieb:
&lt;snip&gt;
&lt;OTRS_CUSTOMER_EMAIL[30]&gt;
&lt;snip&gt;

Bitte um Bearbeitung:

&lt;OTRS_CONFIG_HttpType&gt;://&lt;OTRS_CONFIG_FQDN&gt;/&lt;OTRS_CONFIG_ScriptAlias&gt;index.pl?Action=AgentZoom&amp;TicketID=&lt;OTRS_TICKET_TicketID&gt;

Ihr OTRS Benachrichtigungs-Master</Data>
        <Data Key="create_by">1</Data>
        <Data Key="create_time">current_timestamp</Data>
        <Data Key="change_by">1</Data>
        <Data Key="change_time">current_timestamp</Data>
    </Insert>

    <Insert Table="link_type">
        <Data Key="name" Type="Quote">Normal</Data>
        <Data Key="valid_id">1</Data>
        <Data Key="create_by">1</Data>
        <Data Key="create_time">current_timestamp</Data>
        <Data Key="change_by">1</Data>
        <Data Key="change_time">current_timestamp</Data>
    </Insert>
    <Insert Table="link_type">
        <Data Key="name" Type="Quote">ParentChild</Data>
        <Data Key="valid_id">1</Data>
        <Data Key="create_by">1</Data>
        <Data Key="create_time">current_timestamp</Data>
        <Data Key="change_by">1</Data>
        <Data Key="change_time">current_timestamp</Data>
    </Insert>

    <Insert Table="link_state">
        <Data Key="name" Type="Quote">Valid</Data>
        <Data Key="valid_id">1</Data>
        <Data Key="create_by">1</Data>
        <Data Key="create_time">current_timestamp</Data>
        <Data Key="change_by">1</Data>
        <Data Key="change_time">current_timestamp</Data>
    </Insert>
    <Insert Table="link_state">
        <Data Key="name" Type="Quote">Temporary</Data>
        <Data Key="valid_id">1</Data>
        <Data Key="create_by">1</Data>
        <Data Key="create_time">current_timestamp</Data>
        <Data Key="change_by">1</Data>
        <Data Key="change_time">current_timestamp</Data>
    </Insert>

</database>
