t/rt/RT.pod - metacpan.org

=head1 NAME

 t/eg/RT.pm - example schema, based on Request Tracker

=head1 SYNOPSIS

 use RT qw($schema);
 use Tangram;

 my $storage = Tangram::Storage->connect
                    (Tangram::Schema->new($schema),
                     $dsn, $u, $p,
                    );

=head1 DESCRIPTION

This package is a re-modelling of the Request Tracker 3.0.9 database
schema to Tangram.  See the F<t/eg/README.pod> file in the
distribution for more information.

=head2 Request Tracker

Request Tracker is a trouble ticketing system.  See
L<http://fsck.com/projects/rt/> for more.  The site,
L<http://rt.cpan.org/>, runs on RT.

Request Tracker, as an application, is a Mason application written
using an abstraction library called L<DBIx::SearchBuilder>.


=head1 METHOD

The method for this is fairly simple, once you know how Tangram maps
classes to tables.  Interested readers are urged to first read
L<Tangram::Relational::Mappings>.

First, The MySQL schema was manually converted to a Tangram schema,
table by table.  This process took about 60-90 minutes.

Where possible, obvious foreign key relationships and foreign key with
`SortOrder' columns were converted to Tangram::IntrSet and
Tangram::IntrArray relationship, respectively.  One table,
GroupMembers, was done away with entirely.

In several places, there is use of a "Type" column.  It is suspected
that this often could have been done away with - as all
L<Tangram::Ref> fields by default can point to any class, this sort of
carry-on is seldom necessary.

=head1 APPENDICES

=head2 Appendix A - RT database schema

The following SQL file was used for the conversion, and 

 # {{{ Attachments
 
 CREATE TABLE Attachments (
   id INTEGER NOT NULL  AUTO_INCREMENT,
   TransactionId integer NOT NULL  ,
   Parent integer NOT NULL DEFAULT 0  ,
   MessageId varchar(160) NULL  ,
   Subject varchar(255) NULL  ,
   Filename varchar(255) NULL  ,
   ContentType varchar(80) NULL  ,
   ContentEncoding varchar(80) NULL  ,
   Content LONGTEXT NULL  ,
   Headers LONGTEXT NULL  ,
   Creator integer NOT NULL DEFAULT 0  ,
   Created DATETIME NULL  ,
   PRIMARY KEY (id)
 ) TYPE=InnoDB;
 
 CREATE INDEX Attachments2 ON Attachments (TransactionId) ;
 CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId) ;
 # }}}
 
 # {{{ Queues
 CREATE TABLE Queues (
   id INTEGER NOT NULL  AUTO_INCREMENT,
   Name varchar(200) NOT NULL  ,
   Description varchar(255) NULL  ,
   CorrespondAddress varchar(120) NULL  ,
   CommentAddress varchar(120) NULL  ,
   InitialPriority integer NOT NULL DEFAULT 0  ,
   FinalPriority integer NOT NULL DEFAULT 0  ,
   DefaultDueIn integer NOT NULL DEFAULT 0  ,
   Creator integer NOT NULL DEFAULT 0  ,
   Created DATETIME NULL  ,
   LastUpdatedBy integer NOT NULL DEFAULT 0  ,
   LastUpdated DATETIME NULL  ,
   Disabled int2 NOT NULL DEFAULT 0 ,
   PRIMARY KEY (id)
 ) TYPE=InnoDB;
 CREATE UNIQUE INDEX Queues1 ON Queues (Name) ;
 CREATE INDEX Queues2 ON Queues (Disabled) ;
 
 # }}}
 
 # {{{ Links
 
 CREATE TABLE Links (
   id INTEGER NOT NULL  AUTO_INCREMENT,
   Base varchar(240) NULL  ,
   Target varchar(240) NULL  ,
   Type varchar(20) NOT NULL  ,
   LocalTarget integer NOT NULL DEFAULT 0  ,
   LocalBase integer NOT NULL DEFAULT 0  ,
   LastUpdatedBy integer NOT NULL DEFAULT 0  ,
   LastUpdated DATETIME NULL  ,
   Creator integer NOT NULL DEFAULT 0  ,
   Created DATETIME NULL  ,
   PRIMARY KEY (id)
 ) TYPE=InnoDB;
 
 CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type) ;
 CREATE INDEX Links2 ON Links (Base,  Type) ;
 CREATE INDEX Links3 ON Links (Target,  Type) ;
 CREATE INDEX Links4 ON Links (Type,LocalBase);
 
 # }}}
 
 # {{{ Principals
 
 CREATE TABLE Principals (
         id INTEGER  AUTO_INCREMENT not null,
         PrincipalType VARCHAR(16) not null,
         ObjectId integer, # foreign key to Users or Groups, depending
         Disabled int2 NOT NULL DEFAULT 0 ,
         PRIMARY KEY (id)
 ) TYPE=InnoDB;
 
 CREATE INDEX Principals2 ON Principals (ObjectId);
 
 # }}}
 
 # {{{ Groups
 
 CREATE TABLE Groups (
   id INTEGER NOT NULL  AUTO_INCREMENT,
   Name varchar(200) NULL  ,
   Description varchar(255) NULL  ,
   Domain varchar(64),
   Type varchar(64),
   Instance integer,
   PRIMARY KEY (id)
 ) TYPE=InnoDB;
 
 CREATE INDEX Groups1 ON Groups (Domain,Instance,Type,id);
 CREATE INDEX Groups2 On Groups (Type, Instance);   
 
 # }}}
 
 # {{{ ScripConditions
 
 CREATE TABLE ScripConditions (
   id INTEGER NOT NULL  AUTO_INCREMENT,
   Name varchar(200) NULL  ,
   Description varchar(255) NULL  ,
   ExecModule varchar(60) NULL  ,
   Argument varchar(255) NULL  ,
   ApplicableTransTypes varchar(60) NULL  ,
 
   Creator integer NOT NULL DEFAULT 0  ,
   Created DATETIME NULL  ,
   LastUpdatedBy integer NOT NULL DEFAULT 0  ,
   LastUpdated DATETIME NULL  ,
   PRIMARY KEY (id)
 ) TYPE=InnoDB;
 
 # }}}
 
 # {{{ Transactions
 CREATE TABLE Transactions (
   id INTEGER NOT NULL  AUTO_INCREMENT,
   EffectiveTicket integer NOT NULL DEFAULT 0  ,
   Ticket integer NOT NULL DEFAULT 0  ,
   TimeTaken integer NOT NULL DEFAULT 0  ,
   Type varchar(20) NULL  ,
   Field varchar(40) NULL  ,
   OldValue varchar(255) NULL  ,
   NewValue varchar(255) NULL  ,
   Data varchar(255) NULL  ,
 
   Creator integer NOT NULL DEFAULT 0  ,
   Created DATETIME NULL  ,
   PRIMARY KEY (id)
 ) TYPE=InnoDB;
 CREATE INDEX Transactions1 ON Transactions (Ticket);
 CREATE INDEX Transactions2 ON Transactions (EffectiveTicket);
 
 # }}}
 
 # {{{ Scrips 
 
 CREATE TABLE Scrips (
   id INTEGER NOT NULL  AUTO_INCREMENT,
   Description varchar(255),
   ScripCondition integer NOT NULL DEFAULT 0  ,
   ScripAction integer NOT NULL DEFAULT 0  ,
   ConditionRules text NULL  ,
   ActionRules text NULL  ,
   CustomIsApplicableCode text NULL  ,
   CustomPrepareCode text NULL  ,
   CustomCommitCode text NULL  ,
   Stage varchar(32) NULL  ,
   Queue integer NOT NULL DEFAULT 0  ,
   Template integer NOT NULL DEFAULT 0  ,
   Creator integer NOT NULL DEFAULT 0  ,
   Created DATETIME NULL  ,
   LastUpdatedBy integer NOT NULL DEFAULT 0  ,
   LastUpdated DATETIME NULL  ,
   PRIMARY KEY (id)
 ) TYPE=InnoDB;
 
 # }}}
 
 # {{{ ACL
 CREATE TABLE ACL (
   id INTEGER NOT NULL  AUTO_INCREMENT,
   PrincipalType varchar(25) NOT NULL, #"User" "Group", "Owner", "Cc" "AdminCc", "Requestor", "Requestor" 
 
   PrincipalId integer NOT NULL  , #Foreign key to principals
   RightName varchar(25) NOT NULL  ,
   ObjectType varchar(25) NOT NULL  ,
   ObjectId integer NOT NULL default 0,
   DelegatedBy integer NOT NULL default 0, #foreign key to principals with a userid
   DelegatedFrom integer NOT NULL default 0, #foreign key to ACL
   PRIMARY KEY (id)
 ) TYPE=InnoDB;
 
 CREATE INDEX  ACL1 on ACL(RightName, ObjectType, ObjectId,PrincipalType,PrincipalId);
 
 # }}}
 
 # {{{ GroupMembers 
 
 CREATE TABLE GroupMembers (
   id INTEGER NOT NULL  AUTO_INCREMENT,
   GroupId integer NOT NULL DEFAULT 0,
   MemberId integer NOT NULL DEFAULT 0,  #Foreign key to Principals
   PRIMARY KEY (id)
 ) TYPE=InnoDB;
 CREATE UNIQUE INDEX GroupMembers1 on GroupMembers (GroupId, MemberId);
 
 
 # }}}
 
 # {{{ GroupMembersCache
 
 create table CachedGroupMembers (
         id int auto_increment,
         GroupId int, # foreign key to Principals
         MemberId int, # foreign key to Principals
         Via int, #foreign key to CachedGroupMembers. (may point to $self->id)
         ImmediateParentId int, #foreign key to prinicpals.         
                                # this points to the group that the member is
                                # a member of, for ease of deletes.
         Disabled int2 NOT NULL DEFAULT 0 , # if this cached group member is a member of this group by way of a disabled
                                            # group or this group is disabled, this will be set to 1
                                            # this allows us to not find members of disabled subgroups when listing off
                                            # group members recursively.
                                            # Also, this allows us to have the ACL system elide members of disabled groups
         PRIMARY KEY (id)
 ) TYPE=InnoDB;
 
 CREATE INDEX DisGrouMem  on CachedGroupMembers (GroupId,MemberId,Disabled);
 
 # }}}
 
 # {{{ Users
 
 CREATE TABLE Users (
   id INTEGER NOT NULL  AUTO_INCREMENT,
   Name varchar(200) NOT NULL  ,
   Password varchar(40) NULL  ,
   Comments blob NULL  ,
   Signature blob NULL  ,
   EmailAddress varchar(120) NULL  ,
   FreeformContactInfo blob NULL  ,
   Organization varchar(200) NULL  ,
   RealName varchar(120) NULL  ,
   NickName varchar(16) NULL  ,
   Lang varchar(16) NULL  ,
   EmailEncoding varchar(16) NULL  ,
   WebEncoding varchar(16) NULL  ,
   ExternalContactInfoId varchar(100) NULL  ,
   ContactInfoSystem varchar(30) NULL  ,
   ExternalAuthId varchar(100) NULL  ,
   AuthSystem varchar(30) NULL  ,
   Gecos varchar(16) NULL  ,
   HomePhone varchar(30) NULL  ,
   WorkPhone varchar(30) NULL  ,
   MobilePhone varchar(30) NULL  ,
   PagerPhone varchar(30) NULL  ,
   Address1 varchar(200) NULL  ,
   Address2 varchar(200) NULL  ,
   City varchar(100) NULL  ,
   State varchar(100) NULL  ,
   Zip varchar(16) NULL  ,
   Country varchar(50) NULL  ,
   Timezone varchar(50) NULL  ,
   PGPKey text NULL,
 
   Creator integer NOT NULL DEFAULT 0  ,
   Created DATETIME NULL  ,
   LastUpdatedBy integer NOT NULL DEFAULT 0  ,
   LastUpdated DATETIME NULL  ,
   PRIMARY KEY (id)
 ) TYPE=InnoDB;
 
 
 CREATE UNIQUE INDEX Users1 ON Users (Name) ;
 CREATE INDEX Users4 ON Users (EmailAddress);
 
 
 # }}}
 
 # {{{ Tickets
 
 CREATE TABLE Tickets (
   id INTEGER NOT NULL  AUTO_INCREMENT,
   EffectiveId integer NOT NULL DEFAULT 0  ,
   Queue integer NOT NULL DEFAULT 0  ,
   Type varchar(16) NULL  ,
   IssueStatement integer NOT NULL DEFAULT 0  ,
   Resolution integer NOT NULL DEFAULT 0  ,
   Owner integer NOT NULL DEFAULT 0  ,
   Subject varchar(200) NULL DEFAULT '[no subject]' ,
   InitialPriority integer NOT NULL DEFAULT 0  ,
   FinalPriority integer NOT NULL DEFAULT 0  ,
   Priority integer NOT NULL DEFAULT 0  ,
   TimeEstimated integer NOT NULL DEFAULT 0  ,
   TimeWorked integer NOT NULL DEFAULT 0  ,
   Status varchar(10) NULL  ,
   TimeLeft integer NOT NULL DEFAULT 0  ,
   Told DATETIME NULL  ,
   Starts DATETIME NULL  ,
   Started DATETIME NULL  ,
   Due DATETIME NULL  ,
   Resolved DATETIME NULL  ,
 
 
   LastUpdatedBy integer NOT NULL DEFAULT 0  ,
   LastUpdated DATETIME NULL  ,
   Creator integer NOT NULL DEFAULT 0  ,
   Created DATETIME NULL  ,
   Disabled int2 NOT NULL DEFAULT 0 ,
   PRIMARY KEY (id)
 ) TYPE=InnoDB;
 
 CREATE INDEX Tickets1 ON Tickets (Queue, Status) ;
 CREATE INDEX Tickets2 ON Tickets (Owner) ;
 CREATE INDEX Tickets6 ON Tickets (EffectiveId, Type) ;
 
 # }}}
 
 # {{{ ScripActions
 
 CREATE TABLE ScripActions (
   id INTEGER NOT NULL  AUTO_INCREMENT,
   Name varchar(200) NULL  ,
   Description varchar(255) NULL  ,
   ExecModule varchar(60) NULL  ,
   Argument varchar(255) NULL  ,
   Creator integer NOT NULL DEFAULT 0  ,
   Created DATETIME NULL  ,
   LastUpdatedBy integer NOT NULL DEFAULT 0  ,
   LastUpdated DATETIME NULL  ,
   PRIMARY KEY (id)
 ) TYPE=InnoDB;
 
 # }}}
 
 # {{{ Templates
 
 CREATE TABLE Templates (
   id INTEGER NOT NULL  AUTO_INCREMENT,
   Queue integer NOT NULL DEFAULT 0 ,
   Name varchar(200) NOT NULL  ,
   Description varchar(255) NULL  ,
   Type varchar(16) NULL  ,
   Language varchar(16) NULL  ,
   TranslationOf integer NOT NULL DEFAULT 0  ,
   Content blob NULL  ,
   LastUpdated DATETIME NULL  ,
   LastUpdatedBy integer NOT NULL DEFAULT 0  ,
   Creator integer NOT NULL DEFAULT 0  ,
   Created DATETIME NULL  ,
   PRIMARY KEY (id)
 ) TYPE=InnoDB;
 
 # }}}
 
 # {{{ TicketCustomFieldValues 
 
 CREATE TABLE TicketCustomFieldValues (
   id INTEGER NOT NULL  AUTO_INCREMENT,
   Ticket int NOT NULL  ,
   CustomField int NOT NULL  ,
   Content varchar(255) NULL  ,
 
   Creator integer NOT NULL DEFAULT 0  ,
   Created DATETIME NULL  ,
   LastUpdatedBy integer NOT NULL DEFAULT 0  ,
   LastUpdated DATETIME NULL  ,
   PRIMARY KEY (id)
 ) TYPE=InnoDB;
 
 CREATE INDEX TicketCustomFieldValues1 ON TicketCustomFieldValues (CustomField,Ticket,Content); 
 
 # }}}
 
 # {{{ CustomFields
 
 CREATE TABLE CustomFields (
   id INTEGER NOT NULL  AUTO_INCREMENT,
   Name varchar(200) NULL  ,
   Type varchar(200) NULL  ,
   Queue integer NOT NULL DEFAULT 0 ,
   Description varchar(255) NULL  ,
   SortOrder integer NOT NULL DEFAULT 0  ,
 
   Creator integer NOT NULL DEFAULT 0  ,
   Created DATETIME NULL  ,
   LastUpdatedBy integer NOT NULL DEFAULT 0  ,
   LastUpdated DATETIME NULL  ,
   Disabled int2 NOT NULL DEFAULT 0 ,
   PRIMARY KEY (id)
 ) TYPE=InnoDB;
 
 CREATE INDEX CustomFields1 on CustomFields (Disabled, Queue);
 
 
 # }}}
 
 # {{{ CustomFieldValues 
 
 CREATE TABLE CustomFieldValues (
   id INTEGER NOT NULL  AUTO_INCREMENT,
   CustomField int NOT NULL  ,
   Name varchar(200) NULL  ,
   Description varchar(255) NULL  ,
   SortOrder integer NOT NULL DEFAULT 0  ,
 
   Creator integer NOT NULL DEFAULT 0  ,
   Created DATETIME NULL  ,
   LastUpdatedBy integer NOT NULL DEFAULT 0  ,
   LastUpdated DATETIME NULL  ,
   PRIMARY KEY (id)
 ) TYPE=InnoDB;
 
 CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField);
  
 # }}}
 
 
 # {{{ Attributes
 
 CREATE TABLE Attributes (
   id INTEGER NOT NULL  AUTO_INCREMENT,
   Name varchar(255) NULL  ,
   Description varchar(255) NULL  ,
   Content text,
   ContentType varchar(16),
   ObjectType varchar(64),
   ObjectId integer, # foreign key to anything
   Creator integer NOT NULL DEFAULT 0  ,
   Created DATETIME NULL  ,
   LastUpdatedBy integer NOT NULL DEFAULT 0  ,
   LastUpdated DATETIME NULL  ,
   PRIMARY KEY (id)
 ) TYPE=InnoDB;
 
 CREATE INDEX Attributes1 on Attributes(Name);
 CREATE INDEX Attributes2 on Attributes(ObjectType, ObjectId);
 
 # }}}
 
 # {{{ Sessions
 
 # sessions is used by Apache::Session to keep sessions in the database.
 # We should have a reaper script somewhere.
 
 CREATE TABLE sessions (
     id char(32) NOT NULL,
     a_session LONGTEXT,
     LastUpdated TIMESTAMP,
     PRIMARY KEY (id)
 );
 
 # }}}

=cut
	Global
`s`	Focus search bar
`?`	Bring up this help dialog
	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)
	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse
	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)