Question about the data model!

Hello!

I’m in the process of understanding the rt datamodel to clean up our
database. One think I don’t understand if the query:

select Principals.id,PrincipalType from Principals
left join Users on Principals.id=Users.id
left join Groups on Principals.id=Groups.id
where Users.id is NULL and Groups.id is NULL;

should give me an empty result or not. I think in "Principals"
is one entry related to an user or a group?

In our database ther 150k row which related to nothing :-/

best regards

May be you can test RTx::Shredder? :slight_smile:

Sven Sternberger wrote:

Hello!

I’m in the process of understanding the rt datamodel to clean up our
database. One think I don’t understand if the query:

select Principals.id,PrincipalType from Principals
left join Users on Principals.id=Users.id
left join Groups on Principals.id=Groups.id
where Users.id is NULL and Groups.id is NULL;

should give me an empty result or not. I think in "Principals"
is one entry related to an user or a group?
It should be empty, but …

In our database ther 150k row which related to nothing :-/
Oops. I think it’s code in RT::Group::Delete.
# TODO XXX

 # Remove the principal object
 # Remove this group from anything it's a member of.
 # Remove all cached members of this group
 # Remove any rights granted to this group
 # remove any rights delegated by way of this group 

I have 4k records.

Hello!

I’m in the process of understanding the rt datamodel to clean up our
database. One think I don’t understand if the query:

select Principals.id,PrincipalType from Principals
left join Users on Principals.id=Users.id
left join Groups on Principals.id=Groups.id
where Users.id is NULL and Groups.id is NULL;

should give me an empty result or not. I think in "Principals"
is one entry related to an user or a group?

What do these give you?
Select count(id) from Users;
Select count(id) from Groups;
Select count(id) from Principals;

Hello!

What do these give you?
Select count(id) from Users;

=18816

Select count(id) from Groups;

=179553

Select count(id) from Principals;

=346930

which give me a difference of nearly 149k. Could it the result of our
migration? We made several incremental steps.

best regards!

Hello!

What do these give you?
Select count(id) from Users;

=18816

Select count(id) from Groups;

=179553

Select count(id) from Principals;

=346930

which give me a difference of nearly 149k. Could it the result of our
migration? We made several incremental steps.

Are the “extra” principals for missing users or missing groups?

Hi!

Select count(id) from Principals;

=346930

which give me a difference of nearly 149k. Could it the result of our
migration? We made several incremental steps.

Are the “extra” principals for missing users or missing groups?

There are all “groups” entries. I find them referenced in the
table CachedGroupMembers.

best regards

Select count(id) from Principals;
=346930

which give me a difference of nearly 149k. Could it the result of our
migration? We made several incremental steps.

Are the “extra” principals for missing users or missing groups?

There are all “groups” entries. I find them referenced in the
table CachedGroupMembers.

Have you ever deleted tickets from RT? If so, using what tool or
technique?

Hello!

Have you ever deleted tickets from RT? If so, using what tool or
technique?

I regular deleted tickets in rt2 with a script called purge_script.pl
by Steve Poirier and after changing to rt3 now I use the successor
purgedead.pl (with the additional editor Tony Aiuto,Jonas Lincoln and
Dirk Pape).

best regards!

I regular deleted tickets in rt2 with a script called purge_script.pl
by Steve Poirier and after changing to rt3 now I use the successor
purgedead.pl (with the additional editor Tony Aiuto,Jonas Lincoln and
Dirk Pape).

It sounds like this script might not handle everything it needs to.
Where does it come from?

Jesse Vincent wrote:

purge_script.pl by Steve Poirier

It sounds like this script might not handle everything it needs to.
Where does it come from?

I found it in the rt-user mailinglist but at the moment the mailinglist
search engine seems to be not working, so I couldn’t sent you the exact
location. Instead I add the scripts which I used as
attachment.

purge_script.pl (the old one I used for a long time on rt2)
purgedead.pl (the new on I used 2 oder 3 times on the rt3)

best regards!

purge_script.pl (1.79 KB)

purgedead.pl (3.19 KB)

Jesse Vincent wrote:

purge_script.pl by Steve Poirier

It sounds like this script might not handle everything it needs to.
Where does it come from?

I found it in the rt-user mailinglist but at the moment the mailinglist
search engine seems to be not working, so I couldn’t sent you the exact
location. Instead I add the scripts which I used as
attachment.

purge_script.pl (the old one I used for a long time on rt2)
purgedead.pl (the new on I used 2 oder 3 times on the rt3)

Yeah. That script is…somewhat naive about how it all works. It doesn’t
clean out Principals or CachedGroupMembers at all.

Okay!

Yeah. That script is…somewhat naive about how it all works. It doesn’t
clean out Principals or CachedGroupMembers at all.

that sounds not really >> soothing <<.
I think the script is quite popular, so there a lot of people outside
which will have the same problem :open_mouth:

Could you give me a hint how I can “repair” this mess?

best regards

sven

Is it right that an entry in Principals which has no
correspondig entry in Users and Groups could be deleted?

Is it right that that an entry in CachedGroupMember which
has no correspondig entry in Groups or Principals could
be killed?

If the answer are only simple no’s. Is there any point where
I can get more information about the datamodell? I just try
to avoid to read all the perl source code.

best regards

Sven Sternberger wrote:

Is it right that an entry in Principals which has no
correspondig entry in Users and Groups could be deleted?

Yes.

Is it right that that an entry in CachedGroupMember which
has no correspondig entry in Groups or Principals could
be killed?

Sort of. That table “unrolls” groups. So you need to make sure that none
of the row’s children are still there either. We’ve got a fuller
explanation of the whole datamodel in our forthcoming O’Reilly book
"RT Essentials". But as Robert, Darren and I have been finding,
writing a book is slow going.

If the answer are only simple no’s. Is there any point where
I can get more information about the datamodell? I just try
to avoid to read all the perl source code.

The CachedGroupMembers code should be some of the better commented code
in the source tree.