<?xml version="1.0" encoding="utf-8" ?>
<database Name="otrs">
    <TableAlter Name="ticket_index">
        <ColumnChange NameOld="queue" NameNew="queue" Required="true" Size="200" Type="VARCHAR"/>
        <ColumnChange NameOld="s_state" NameNew="s_state" Required="true" Size="200" Type="VARCHAR"/>
        <ColumnChange NameOld="s_lock" NameNew="s_lock" Required="true" Size="200" Type="VARCHAR"/>
    </TableAlter>

<TableCreate Name="gi_webservice_config">
    <Column Name="id" Required="true" PrimaryKey="true" AutoIncrement="true" Type="INTEGER"/>
    <Column Name="name" Required="true" Size="200" Type="VARCHAR"/>
    <Column Name="config" Required="true" Type="LONGBLOB"/>
    <Column Name="config_md5" Required="true" Size="32" 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="gi_webservice_config_name">
        <UniqueColumn Name="name"/>
    </Unique>
    <Unique Name="gi_webservice_config_config_md5">
        <UniqueColumn Name="config_md5"/>
    </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="gi_webservice_config_history">
    <Column Name="id" Required="true" PrimaryKey="true" AutoIncrement="true" Type="BIGINT"/>
    <Column Name="config_id" Required="true" Type="INTEGER"/>
    <Column Name="config" Required="true" Type="LONGBLOB"/>
    <Column Name="config_md5" Required="true" Size="32" Type="VARCHAR"/>
    <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="gi_webservice_config_history_config_md5">
        <UniqueColumn Name="config_md5"/>
    </Unique>
    <ForeignKey ForeignTable="gi_webservice_config">
        <Reference Local="config_id" Foreign="id"/>
    </ForeignKey>
    <ForeignKey ForeignTable="users">
        <Reference Local="create_by" Foreign="id"/>
        <Reference Local="change_by" Foreign="id"/>
    </ForeignKey>
</TableCreate>

<TableCreate Name="scheduler_task_list">
    <Column Name="id" Required="true" PrimaryKey="true" AutoIncrement="true" Type="BIGINT"/>
    <Column Name="task_data" Required="true" Size="8000" Type="VARCHAR"/>
    <Column Name="task_data_md5" Required="true" Size="32" Type="VARCHAR"/>
    <Column Name="task_type" Required="true" Size="200" Type="VARCHAR"/>
    <Column Name="due_time" Required="true" Type="DATE"/>
    <Column Name="create_time" Required="true" Type="DATE"/>
    <Unique Name="scheduler_task_list_task_data_md5">
        <UniqueColumn Name="task_data_md5"/>
    </Unique>
</TableCreate>

<TableCreate Name="gi_debugger_entry">
    <Column Name="id" Required="true" PrimaryKey="true" AutoIncrement="true" Type="BIGINT"/>
    <Column Name="communication_id" Required="true" Size="32" Type="VARCHAR"/>
    <Column Name="communication_type" Required="true" Size="50" Type="VARCHAR"/>
    <Column Name="remote_ip" Required="false" Size="50" Type="VARCHAR"/>
    <Column Name="webservice_id" Required="true" Type="INTEGER"/>
    <Column Name="create_time" Required="true" Type="DATE"/>
    <Unique Name="gi_debugger_entry_communication_id">
        <UniqueColumn Name="communication_id"/>
    </Unique>
    <Index Name="gi_debugger_entry_create_time">
        <IndexColumn Name="create_time"/>
    </Index>
    <ForeignKey ForeignTable="gi_webservice_config">
        <Reference Local="webservice_id" Foreign="id"/>
    </ForeignKey>
</TableCreate>

<TableCreate Name="gi_debugger_entry_content">
    <Column Name="id" Required="true" PrimaryKey="true" AutoIncrement="true" Type="BIGINT"/>
    <Column Name="gi_debugger_entry_id" Required="true" Type="BIGINT"/>
    <Column Name="debug_level" Required="true" Size="50" Type="VARCHAR"/>
    <Column Name="subject" Required="true" Size="255" Type="VARCHAR"/>
    <Column Name="content" Required="false" Type="LONGBLOB"/>
    <Column Name="create_time" Required="true" Type="DATE"/>
    <Index Name="gi_debugger_entry_content_create_time">
        <IndexColumn Name="create_time"/>
    </Index>
    <Index Name="gi_debugger_entry_content_debug_level">
        <IndexColumn Name="debug_level"/>
    </Index>
    <ForeignKey ForeignTable="gi_debugger_entry">
        <Reference Local="gi_debugger_entry_id" Foreign="id"/>
    </ForeignKey>
</TableCreate>

<TableCreate Name="gi_object_lock_state">
    <Column Name="webservice_id" Required="true" Type="INTEGER"/>
    <Column Name="object_type" Required="true" Size="30" Type="VARCHAR"/>
    <Column Name="object_id" Required="true" Type="BIGINT"/>
    <Column Name="lock_state" Required="true" Size="30" Type="VARCHAR"/>
    <Column Name="lock_state_counter" Required="true" Type="INTEGER"/>
    <Column Name="create_time" Required="true" Type="DATE"/>
    <Column Name="change_time" Required="true" Type="DATE"/>
    <Unique>
        <UniqueColumn Name="webservice_id"/>
        <UniqueColumn Name="object_type"/>
        <UniqueColumn Name="object_id"/>
    </Unique>
    <Index Name="object_lock_state_list_state">
        <IndexColumn Name="webservice_id"/>
        <IndexColumn Name="object_type"/>
        <IndexColumn Name="object_id"/>
        <IndexColumn Name="lock_state"/>
    </Index>
    <ForeignKey ForeignTable="gi_webservice_config">
        <Reference Local="webservice_id" Foreign="id"/>
    </ForeignKey>
</TableCreate>

<Insert Table="ticket_history_type">
    <Data Key="name" Type="Quote">EscalationSolutionTimeStop</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="ticket_history_type">
    <Data Key="name" Type="Quote">EscalationResponseTimeStart</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="ticket_history_type">
    <Data Key="name" Type="Quote">EscalationUpdateTimeStart</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="ticket_history_type">
    <Data Key="name" Type="Quote">EscalationSolutionTimeStart</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="ticket_history_type">
    <Data Key="name" Type="Quote">EscalationResponseTimeNotifyBefore</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="ticket_history_type">
    <Data Key="name" Type="Quote">EscalationUpdateTimeNotifyBefore</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="ticket_history_type">
    <Data Key="name" Type="Quote">EscalationSolutionTimeNotifyBefore</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="ticket_history_type">
    <Data Key="name" Type="Quote">EscalationResponseTimeStop</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="ticket_history_type">
    <Data Key="name" Type="Quote">EscalationUpdateTimeStop</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>

<TableCreate Name="smime_signer_cert_relations">
    <Column Name="id" Required="true" PrimaryKey="true" AutoIncrement="true" Type="INTEGER"/>
    <Column Name="cert_hash" Required="true" Size="8" Type="VARCHAR"/>
    <Column Name="cert_fingerprint" Required="true" Size="59" Type="VARCHAR"/>
    <Column Name="ca_hash" Required="true" Size="8" Type="VARCHAR"/>
    <Column Name="ca_fingerprint" Required="true" Size="59" Type="VARCHAR"/>
    <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"/>
    <ForeignKey ForeignTable="users">
        <Reference Local="create_by" Foreign="id"/>
        <Reference Local="change_by" Foreign="id"/>
    </ForeignKey>
</TableCreate>

<TableAlter Name="process_id">
    <ColumnAdd Name="process_change" Required="true" Type="INTEGER"/>
</TableAlter>

<TableAlter Name="ticket_flag">
    <UniqueCreate Name="ticket_flag_per_user">
        <UniqueColumn Name="ticket_id"/>
        <UniqueColumn Name="ticket_key"/>
        <UniqueColumn Name="create_by"/>
    </UniqueCreate>
</TableAlter>

<TableCreate Name="dynamic_field_value">
    <Column Name="id" Required="true" PrimaryKey="true" AutoIncrement="true" Type="INTEGER"/>
    <!-- foreign key to the dynamic_field -->
    <Column Name="field_id" Required="true" Type="INTEGER"/>
    <!-- the current object to link to (e. g. ticket or article ID) -->
    <Column Name="object_id" Required="true" Type="BIGINT"/>
    <!-- for value we have different field types to store data, can be extended in future -->
    <Column Name="value_text" Required="false" Type="VARCHAR" Size="3800" />
    <Column Name="value_date" Required="false" Type="DATE" />
    <Column Name="value_int" Required="false" Type="BIGINT" />

    <!-- search objects with certain field content -->
    <Index Name="index_search_date">
        <IndexColumn Name="field_id"/>
        <IndexColumn Name="value_date"/>
    </Index>
    <Index Name="index_search_int">
        <IndexColumn Name="field_id"/>
        <IndexColumn Name="value_int"/>
    </Index>
    <Index Name="index_field_values">
        <IndexColumn Name="field_id"/>
        <IndexColumn Name="object_id"/>
    </Index>
    <ForeignKey ForeignTable="dynamic_field">
        <Reference Local="field_id" Foreign="id"/>
    </ForeignKey>
</TableCreate>

<TableCreate Name="dynamic_field">
    <Column Name="id" Required="true" PrimaryKey="true" AutoIncrement="true" Type="INTEGER"/>
    <!-- the internal field name to be used in response templates, allow only lowercase letters and numbers -->
    <Column Name="name" Required="true" Type="VARCHAR" Size="200"/>
    <!-- label, always translate -->
    <Column Name="label" Required="true" Type="VARCHAR" Size="200"/>
    <!-- order defines the DF backend display order on all the masks -->
    <Column Name="field_order" Required="true" Type="INTEGER"/>
    <!-- field type selects the DF backend to use for this field -->
    <Column Name="field_type" Required="true" Type="VARCHAR" Size="200"/>
    <!-- this controls which object the dynamic field links to; currently we have "Ticket" and "Article" -->
    <Column Name="object_type" Required="true" Type="VARCHAR" Size="200"/>
    <!-- config stored as YAML like in the web service configuration -->
    <Column Name="config" Type="LONGBLOB"/>
    <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>
        <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>

<TableAlter Name="mail_account">
    <ColumnAdd Name="imap_folder" Required="false" Size="250" Type="VARCHAR"/>
</TableAlter>

</database>
