DatabaseAdmin

The following is a brief overview of the RT (3.0.x) schema and some tips on examining the database. Also, very occasionally you may wish to modify the database directly (e.g. delete bogus information).

How to do these things is roughly described here.

NOTE: Nobody guarantee that this document describes all relations in the database.

NOTE: Making direct modifications to the database is not recommended, but is sometimes required. Should you do so, ensure that you backup your database first, and ensure its integrity afterwards.

Tables

The RT DBSchema can be thought of in 5 broad groupings:

• Groups and Users (groups, users, groupmembers, cachedgroupmembers, principals, acl)
• Templates and Scrips (scripconditions, scripactions, scrips, templates)
• Infrastructure (queues, customfields, customfieldvalues)
• Tickets and related information (tickets, transactions, ticketcustomfieldvalues, links, attachments)
• Sessions

In mysql or postgresql you can use the following commands to examine the schema:

• help
• help command
• \dt [ table ]
• \ds [ sequence ]

Relations

Each object is identified uniquely by an integer id (sequence) which is incremented for each new object.

You can see the relationships among objects by following id linkages. e.g.

• ticket -> queue
• ticketcustomfieldvalue -> ticket
• transaction -> ticket
• link -> target
• attachment -> transactionid

Danger way to delete tickets

NOTE: Original author forget a lot of details.
1) RT creates role groups for each ticket.
2) Attachments have parent/child relationship.
3) Each role group has principal record.
4) each role group can have members
5) each reacord in group members has one or more related records in cached group members. IMHO you shouldn't use this process to delete any records in RT DB
--RuslanZakirov

NOTE: Also original author suggest to use copy&paste method to delete related records that is bad dy design. --RuslanZakirov

If you really must delete or modify an object, first determine all linkages to that object. e.g.

• select * from tickets where id = <some id>;
• select id from transactions where ticket = <your ticket id>;
• select id from ticketcustomfieldvalues where ticket = <your ticket id>;
• select id from links where target = <your ticket id>;
• select id from attachments where transactionid = <your transaction id>;

Then to delete a given ticket, use the following:

• delete from attachments where id in ( <attachment ids> );
• delete from links where id in ( <link ids> );
• delete from ticketcustomfieldvalues where id in ( <ticketcustomfieldvalue ids> );
• delete from transactions where id in ( <transaction ids> );
• delete from tickets where id = <your ticket id>;

If you have a large number of bogus tickets to delete you could use a script (perl, shell, sql) to determine which tickets need to be deleted and to do the deletion.

ANOTHER NOTE:

As a new ticket is being inserted, records are also generated in the following tables: principals, cachedgroupmembers, groupmembers, groups. So, as you delete a ticket, you also want to purge records that directly or indirectly reference a range of tickets to be deleted from these tables.

DO NOT USE THIS RECIPE. PLEASE USE RT SHREDDER, BUILT IN TO RT 3.8 AND NEWER

You may want to do something like this (in addition to the steps above):

1) define tickets to delete:
  $tickets_to_delete = "age(tickets.resolved) > '6 months'";

2) delete tickets and all related records:
- delete from principals where
    id=groups.id and groups.instance=tickets.id as int)
    and $tickets_to_delete;
- delete from cachedgroupmembers where groupid=groups.id
    and groups.instance=tickets.id and $tickets_to_delete;
- delete from groupmembers where groupid=groups.id and
    groups.instance=tickets.id and $tickets_to_delete;
- delete from groups where instance=tickets.id and $tickets_to_delete;

                                                    -AnyaFiglin