diff options
Diffstat (limited to 'rt/etc')
-rw-r--r-- | rt/etc/acl.Oracle | 9 | ||||
-rwxr-xr-x | rt/etc/acl.Pg | 39 | ||||
-rwxr-xr-x | rt/etc/acl.mysql | 4 | ||||
-rwxr-xr-x | rt/etc/config.pm | 473 | ||||
-rw-r--r-- | rt/etc/rt.spec | 137 | ||||
-rw-r--r-- | rt/etc/schema.Oracle | 287 | ||||
-rwxr-xr-x | rt/etc/schema.Pg | 267 | ||||
-rwxr-xr-x | rt/etc/schema.mysql | 267 | ||||
-rw-r--r-- | rt/etc/schema.pm | 349 |
9 files changed, 1832 insertions, 0 deletions
diff --git a/rt/etc/acl.Oracle b/rt/etc/acl.Oracle new file mode 100644 index 000000000..59d35a028 --- /dev/null +++ b/rt/etc/acl.Oracle @@ -0,0 +1,9 @@ +CREATE USER !!DB_RT_USER!! identified by !!DB_RT_PASS!! +temporary tablespace TEMP +default tablespace USERS +quota unlimited on USERS; + +grant connect, resource to !!DB_RT_USER!!; + +exit; + diff --git a/rt/etc/acl.Pg b/rt/etc/acl.Pg new file mode 100755 index 000000000..13ac41dcf --- /dev/null +++ b/rt/etc/acl.Pg @@ -0,0 +1,39 @@ +drop user !!DB_RT_USER!!; +create user !!DB_RT_USER!! with password '!!DB_RT_PASS!!' NOCREATEDB NOCREATEUSER; + +grant select, insert, update, delete on Groups to !!DB_RT_USER!!; +grant select, insert, update, delete on Groups_id_seq to !!DB_RT_USER!!; +grant select, insert, update, delete on ACL to !!DB_RT_USER!!; +grant select, insert, update, delete on ACL_id_seq to !!DB_RT_USER!!; +grant select, insert, update, delete on Watchers to !!DB_RT_USER!!; +grant select, insert, update, delete on Watchers_id_seq to !!DB_RT_USER!!; +grant select, insert, update, delete on Links to !!DB_RT_USER!!; +grant select, insert, update, delete on Links_id_seq to !!DB_RT_USER!!; +grant select, insert, update, delete on Users to !!DB_RT_USER!!; +grant select, insert, update, delete on Users_id_seq to !!DB_RT_USER!!; +grant select, insert, update, delete on Tickets to !!DB_RT_USER!!; +grant select, insert, update, delete on Tickets_id_seq to !!DB_RT_USER!!; +grant select, insert, update, delete on GroupMembers to !!DB_RT_USER!!; +grant select, insert, update, delete on GroupMembers_id_seq to !!DB_RT_USER!!; +grant select, insert, update, delete on Queues to !!DB_RT_USER!!; +grant select, insert, update, delete on Queues_id_seq to !!DB_RT_USER!!; +grant select, insert, update, delete on Transactions to !!DB_RT_USER!!; +grant select, insert, update, delete on Transactions_id_seq to !!DB_RT_USER!!; +grant select, insert, update, delete on ScripActions to !!DB_RT_USER!!; +grant select, insert, update, delete on ScripActions_id_seq to !!DB_RT_USER!!; +grant select, insert, update, delete on ScripConditions to !!DB_RT_USER!!; +grant select, insert, update, delete on ScripConditions_id_seq to !!DB_RT_USER!!; +grant select, insert, update, delete on Scrips to !!DB_RT_USER!!; +grant select, insert, update, delete on Scrips_id_seq to !!DB_RT_USER!!; +grant select, insert, update, delete on Attachments to !!DB_RT_USER!!; +grant select, insert, update, delete on Attachments_id_seq to !!DB_RT_USER!!; +grant select, insert, update, delete on Templates to !!DB_RT_USER!!; +grant select, insert, update, delete on Templates_id_seq to !!DB_RT_USER!!; +grant select, insert, update, delete on Keywords to !!DB_RT_USER!!; +grant select, insert, update, delete on Keywords_id_seq to !!DB_RT_USER!!; +grant select, insert, update, delete on ObjectKeywords to !!DB_RT_USER!!; +grant select, insert, update, delete on ObjectKeywords_id_seq to !!DB_RT_USER!!; +grant select, insert, update, delete on KeywordSelects to !!DB_RT_USER!!; +grant select, insert, update, delete on KeywordSelects_id_seq to !!DB_RT_USER!!; + + diff --git a/rt/etc/acl.mysql b/rt/etc/acl.mysql new file mode 100755 index 000000000..7feb376f5 --- /dev/null +++ b/rt/etc/acl.mysql @@ -0,0 +1,4 @@ + +DELETE FROM user WHERE user like '!!DB_RT_USER!!'; +DELETE FROM db where db LIKE '!!DB_DATABASE!!'; +GRANT SELECT,INSERT,CREATE,INDEX,UPDATE,DELETE ON !!DB_DATABASE!!.* TO !!DB_RT_USER!!@!!DB_RT_HOST!! IDENTIFIED BY '!!DB_RT_PASS!!'; diff --git a/rt/etc/config.pm b/rt/etc/config.pm new file mode 100755 index 000000000..52b1a0b25 --- /dev/null +++ b/rt/etc/config.pm @@ -0,0 +1,473 @@ +# $Header: /home/cvs/cvsroot/freeside/rt/etc/Attic/config.pm,v 1.1 2002-08-12 06:17:07 ivan Exp $ + +package RT; + +# {{{ Base Configuration + +# $rtname the string that RT will look for in mail messages to +# figure out what ticket a new piece of mail belongs to + +# Your domain name is recommended, so as not to pollute the namespace. +# once you start using a given tag, you should probably never change it. +# (otherwise, mail for existing tickets won't get put in the right place + +$rtname="example.com"; + +# You should set this to your organization's DNS domain. For example, +# fsck.com or asylum.arkham.ma.us. It's used by the linking interface to +# guarantee that ticket URIs are unique and easy to construct. + +$Organization = "example.com"; + +# $user_passwd_min defines the minimum length for user passwords. Setting +# it to 0 disables this check +$MinimumPasswordLength = "5"; + +# $Timezone is used to convert times entered by users into GMT and back again +# It should be set to a timezone recognized by your local unix box. +$Timezone = 'US/Eastern'; + +# LogDir is where RT writes its logfiles. +# This directory should be writable by your rt group +$LogDir = "!!RT_LOG_PATH!!"; + +# }}} + +# {{{ Database Configuration + +# Database driver beeing used - i.e. MySQL. +$DatabaseType="!!DB_TYPE!!"; + +# The domain name of your database server +# If you're running mysql and it's on localhost, +# leave it blank for enhanced performance +$DatabaseHost="!!DB_HOST!!"; + +# The port that your database server is running on. Ignored unless it's +# a positive integer. It's usually safe to leave this blank +$DatabasePort="!!DB_PORT!!"; + + +#The name of the database user (inside the database) +$DatabaseUser='!!DB_RT_USER!!'; + +# Password the DatabaseUser should use to access the database +$DatabasePassword='!!DB_RT_PASS!!'; + + +# The name of the RT's database on your database server +$DatabaseName='!!DB_DATABASE!!'; + +# If you're using Postgres and have compiled in SSL support, +# set DatabaseRequireSSL to 1 to turn on SSL communication +$DatabaseRequireSSL=undef; + +# }}} + +# {{{ Incoming mail gateway configuration + + +# OwnerEmail is the address of a human who manages RT. RT will send +# errors generated by the mail gateway to this address. This address +# should _not_ be an address that's managed by your RT instance. + +$OwnerEmail = 'root'; + +# If $LoopsToRTOwner is defined, RT will send mail that it believes +# might be a loop to $RT::OwnerEmail + +$LoopsToRTOwner = 1; + +# If $StoreLoopss is defined, RT will record messages that it believes +# to be part of mail loops. +# As it does this, it will try to be careful not to send mail to the +# sender of these messages + +$StoreLoops = undef; + + +# $MaxAttachmentSize sets the maximum size (in bytes) of attachments stored +# in the database. + +# For mysql and oracle, we set this size at 10 megabytes. +# If you're running a postgres version earlier than 7.1, you will need +# to drop this to 8192. (8k) + +$MaxAttachmentSize = 10000000; + +# $TruncateLongAttachments: if this is set to a non-undef value, +# RT will truncate attachments longer than MaxAttachmentLength. + +$TruncateLongAttachments = undef; + + +# $DropLongAttachments: if this is set to a non-undef value, +# RT will silently drop attachments longer than MaxAttachmentLength. + +$DropLongAttachments = undef; + +# If $ParseNewMessageForTicketCcs is true, RT will attempt to divine +# Ticket 'Cc' watchers from the To and Cc lines of incoming messages +# Be forewarned that if you have _any_ addresses which forward mail to +# RT automatically and you enable this option without modifying +# "IsRTAddress" below, you will get yourself into a heap of trouble. +# And well, this is free software, so there isn't a warrantee, but +# I disclaim all ability to help you if you do enable this without +# modifying IsRTAddress below. + +$ParseNewMessageForTicketCcs = undef; + +# IsRTAddress is used to make sure RT doesn't add itself as a ticket CC if +# the setting above is enabled. + +sub IsRTAddress { + my $address = shift; + + # Example: the following rule would tell RT not to Cc + # "tickets@noc.example.com" + # return(1) if ($address =~ /^tickets\@noc.example.com$/i); + + return(undef) +} + +# CanonicalizeAddress converts email addresses into canonical form. +# it takes one email address in and returns the proper canonical +# form. You can dump whatever your proper local config is in here + +sub CanonicalizeAddress { + my $email = shift; + # Example: the following rule would treat all email + # coming from a subdomain as coming from second level domain + # foo.com + #$email =~ s/\@(.*).foo.com/\@foo.com/; + return ($email) +} + +# If $LookupSenderInExternalDatabase is defined, RT will attempt to +# verify the incoming message sender with a known source, using the +# LookupExternalUserInfo routine below + +$LookupSenderInExternalDatabase = undef; + +# If $SenderMustExistInExternalDatabase is true, RT will refuse to +# create non-privileged accounts for unknown users if you are using +# the "LookupSenderInExternalDatabase" option. +# Instead, an error message will be mailed and RT will forward the +# message to $RTOwner. +# +# If you are not using $LookupSenderInExternalDatabase, this option +# has no effect. +# +# If you define an AutoRejectRequest template, RT will use this +# template for the rejection message. + +$SenderMustExistInExternalDatabase = undef; + +# LookupExternalUserInfo is a site-definable method for synchronizing +# incoming users with an external data source. +# +# This routine takes a tuple of EmailAddress and FriendlyName +# EmailAddress is the user's email address, ususally taken from +# an email message's From: header. +# FriendlyName is a freeform string, ususally taken from the "comment" +# portion of an email message's From: header. +# +# It returns (FoundInExternalDatabase, ParamHash); +# +# FoundInExternalDatabase must be set to 1 before return if the user was +# found in the external database. +# +# ParamHash is a Perl parameter hash which can contain at least the following +# fields. These fields are used to populate RT's users database when the user +# is created +# +# EmailAddress is the email address that RT should use for this user. +# Name is the 'Name' attribute RT should use for this user. +# 'Name' is used for things like access control and user lookups. +# RealName is what RT should display as the user's name when displaying +# 'friendly' names + +sub LookupExternalUserInfo { + my ($EmailAddress, $RealName) = @_; + + my $FoundInExternalDatabase = 1; + my %params = {}; + + #Name is the RT username you want to use for this user. + $params{'Name'} = $EmailAddress; + $params{'EmailAddress'} = $EmailAddress; + $params{'RealName'} = $RealName; + + # See RT's contributed code for examples. + # http://www.fsck.com/pub/rt/contrib/ + return ($FoundInExternalDatabase, %params); +} + +# }}} + +# {{{ Outgoing mail configuration + +# RT is designed such that any mail which already has a ticket-id associated +# with it will get to the right place automatically. + +# $CorrespondAddress and $CommentAddress are the default addresses +# that will be listed in From: and Reply-To: headers of correspondence +# and comment mail tracked by RT, unless overridden by a queue-specific +# address. + +$CorrespondAddress='RT::CorrespondAddress.not.set'; + +$CommentAddress='RT::CommentAddress.not.set'; + + +#Sendmail Configuration + +# $MailCommand defines which method RT will use to try to send mail +# We know that 'sendmailpipe' works fairly well. +# If 'sendmailpipe' doesn't work well for you, try 'sendmail' +# +# Note that you should remove the '-t' from $SendmailArguments +# if you use 'sendmail rather than 'sendmailpipe' + +$MailCommand = 'sendmailpipe'; + +# $SendmailArguments defines what flags to pass to $Sendmail +# assuming you picked 'sendmail' or 'sendmailpipe' as the $MailCommand above. +# If you picked 'sendmailpipe', you MUST add a -t flag to $SendmailArguments + +# These options are good for most sendmail wrappers and workalikes +$SendmailArguments="-oi -t"; + +# These arguments are good for sendmail brand sendmail 8 and newer +#$SendmailArguments="-oi -t -ODeliveryMode=b -OErrorMode=m"; + +# If you selected 'sendmailpipe' above, you MUST specify the path +# to your sendmail binary in $SendmailPath. +# !! If you did not # select 'sendmailpipe' above, this has no effect!! +$SendmailPath = "/usr/sbin/sendmail"; + +# RT can optionally set a "Friendly" 'To:' header when sending messages to +# Ccs or AdminCcs (rather than having a blank 'To:' header. + +# This feature DOES NOT WORK WITH SENDMAIL[tm] BRAND SENDMAIL +# If you are using sendmail, rather than postfix, qmail, exim or some other MTA, +# you _must_ disable this option. + +$UseFriendlyToLine = 0; + + +# }}} + +# {{{ Logging + +# Logging. The default is to log anything except debugging +# information to a logfile. Check the Log::Dispatch POD for +# information about how to get things by syslog, mail or anything +# else, get debugging info in the log, etc. + +# It might generally make +# sense to send error and higher by email to some administrator. +# If you do this, be careful that this email isn't sent to this RT instance. + + +# the minimum level error that will be logged to the specific device. +# levels from lowest to highest: +# debug info notice warning error critical alert emergency + + +# Mail loops will generate a critical log message. + +$LogToScreen = 'error'; +$LogToFile = 'error'; +#$LogToFileNamed = "$LogDir/rt.log.".$$.".".$<; #log to rt.log.<pid>.<user> +$LogToFileNamed = "$LogDir/rt.log".$<; #log to rt.log.user; + +# }}} + +# {{{ Web interface configuration + + + +# Define the directory name to be used for images in rt web +# documents. + +# If you're putting the web ui somewhere other than at the root of +# your server +# $WebPath requires a leading / but no trailing / + +$WebPath = ""; + +# This is the Scheme, server and port for constructing urls to webrt +# $WebBaseURL doesn't need a trailing / + +$WebBaseURL = "http://RT::WebBaseURL.not.configured:80"; + +$WebURL = $WebBaseURL . $WebPath . "/"; + + + +# $WebImagesURL points to the base URL where RT can find its images. +# If you're running the FastCGI version of the RT web interface, +# you should make RT's WebRT/html/NoAuth/images directory available on +# a static web server and supply that URL as $WebImagesURL. + +$WebImagesURL = $WebURL."NoAuth/images/"; + +# $RTLogoURL points to the URL of the RT logo displayed in the web UI + +$LogoURL = $WebImagesURL."rt.jpg"; + +# If $WebExternalAuth is defined, RT will defer to the environment's +# REMOTE_USER variable. + +$WebExternalAuth = undef; + +# $MasonComponentRoot is where your rt instance keeps its mason html files +# (this should be autoconfigured during 'make install' or 'make upgrade') + +$MasonComponentRoot = "!!MASON_HTML_PATH!!"; + +# $MasonLocalComponentRoot is where your rt instance keeps its site-local +# mason html files. +# (this should be autoconfigured during 'make install' or 'make upgrade') + +$MasonLocalComponentRoot = "!!MASON_LOCAL_HTML_PATH!!"; + +# $MasonDataDir Where mason keeps its datafiles +# (this should be autoconfigured during 'make install' or 'make upgrade') + +$MasonDataDir = "!!MASON_DATA_PATH!!"; + +# RT needs to put session data (for preserving state between connections +# via the web interface) +$MasonSessionDir = "!!MASON_SESSION_PATH!!"; + + + +#This is from tobias' prototype web search UI. it may stay and it may go. +%WebOptions= + ( + # This is for putting in more user-actions at the Transaction + # bar. I will typically add "Enter bug in Bugzilla" here.: + ExtraTransactionActions => sub { return ""; }, + + # Here you can modify the list view. Be aware that the web + # interface might crash if TicketAttribute is wrongly set. + + QueueListingCols => + [ + { Header => 'Id', + TicketLink => 1, + TicketAttribute => 'Id' + }, + + { Header => 'Subject', + TicketAttribute => 'Subject' + }, + { Header => 'Requestor(s)', + TicketAttribute => 'RequestorsAsString' + }, + { Header => 'Status', + TicketAttribute => 'Status' + }, + + + { Header => 'Queue', + TicketAttribute => 'QueueObj->Name' + }, + + + + { Header => 'Told', + TicketAttribute => 'ToldObj->AgeAsString' + }, + + { Header => 'Age', + TicketAttribute => 'CreatedObj->AgeAsString' + }, + + { Header => 'Last', + TicketAttribute => 'LastUpdatedObj->AgeAsString' + }, + + # TODO: It would be nice with a link here to the Owner and all + # other request owned by this Owner. + { Header => 'Owner', + TicketAttribute => 'OwnerObj->Name' + }, + + + { Header => 'Take', + TicketLink => 1, + Constant => 'Take', + ExtraLinks => '&Action=Take' + }, + + ] + ); + +# }}} + +# {{{ RT Linking Interface + +# $TicketBaseURI is the Base path of the URI for local tickets + +# You shouldn't need to touch this. it's used to link tickets both locally +# and remotely + +$TicketBaseURI = "fsck.com-rt://$Organization/$rtname/ticket/"; + +# A hash table of conversion subs to be used for transforming RT Link +# URIs to URLs in the web interface. If you want to use RT towards +# locally installed databases, this is the right place to configure it. + +%URI2HTTP= + ( + 'http' => sub {return @_;}, + 'https' => sub {return @_;}, + 'ftp' => sub {return @_;}, + 'fsck.com-rt' => sub {warn "stub!";}, + 'mozilla.org-bugzilla' => sub {warn "stub!"}, + 'fsck.com-kb' => sub {warn "stub!"} + ); + + +# A hash table of subs for fetching content from an URI +%ContentFromURI= + ( + 'fsck.com-rt' => sub {warn "stub!";}, + 'mozilla.org-bugzilla' => sub {warn "stub!"}, + 'fsck.com-kb' => sub {warn "stub!"} + ); + +# }}} + +# {{{ No User servicable parts inside + +############################################ +############################################ +############################################ +# +# Don't edit anything below this line unless you really know +# what you're doing +# +# +############################################ +############################################ + +# TODO: get this stuff out of the config file and into RT.pm + +#Set up us the timezone +$ENV{'TZ'} = $Timezone; #TODO: Bogus hack to deal with Date::Manip whining + +# Configure sendmail if we're using Entity->send('sendmail') +if ($MailCommand eq 'sendmail') { + $MailParams = $SendmailArguments; +} + + + +# }}} + + +1; diff --git a/rt/etc/rt.spec b/rt/etc/rt.spec new file mode 100644 index 000000000..14200c1f3 --- /dev/null +++ b/rt/etc/rt.spec @@ -0,0 +1,137 @@ +Summary: rt Request Tracker + +Name: rt +Version: 2.0.9pre5 +Release: 1 +Group: Applications/Web +Packager: Jesse Vincent <jesse@bestpractical.com> +Vendor: http://www.fsck.com/projects/rt +Requires: perl +Requires: mod_perl > 1.22 +Requires: perl-DBI >= 1.18 +Requires: perl-DBIx-DataSource >= 0.02 +Requires: perl-DBIx-SearchBuilder >= 0.47 +Requires: perl-HTML-Parser +Requires: perl-MLDBM +Requires: perl-libnet +Requires: perl-CGI.pm >= 2.78 +Requires: perl-Params-Validate >= 0.02 +Requires: perl-HTML-Mason >= 0.896 +Requires: perl-libapreq +Requires: perl-Apache-Session >= 1.53 +Requires: perl-MIME-tools >= 5.411 +Requires: perl-MailTools >= 1.20 +Requires: perl-Getopt-Long >= 2.24 +Requires: perl-Tie-IxHash +Requires: perl-TimeDate +Requires: perl-Time-HiRes +Requires: perl-Text-Wrapper +Requires: perl-Text-Template +Requires: perl-File-Spec >= 0.8 +Requires: perl-FreezeThaw +Requires: perl-Storable +Requires: perl-File-Temp +Requires: perl-Log-Dispatch >= 1.6 + +Source: http://www.fsck.com/pub/rt/release/%{name}.tar.gz + +Copyright: GPL +BuildRoot: /var/tmp/rt-root + +%description +RT is an industrial-grade ticketing system. It lets a group +of people intelligently and efficiently manage requests +submitted by a community of users. RT is used by systems +administrators, customer support staffs, NOCs, developers +and even marketing departments at over a thousand sites +around the world. + +%prep +groupadd rt || true +%setup -q -n %{name} + +%build + +%install + +if [ x$RPM_BUILD_ROOT != x ]; then +rm -rf $RPM_BUILD_ROOT +fi + +# +# Perform all the non-site specfic steps whilst building the package +# +make dirs libs-install html-install bin-install DESTDIR=$RPM_BUILD_ROOT +# +# fixperms needs these, so make fake empty files +touch $RPM_BUILD_ROOT/opt/rt2/etc/insertdata $RPM_BUILD_ROOT/opt/rt2/etc/config.pm +make fixperms insert-install WEB_USER=www DESTDIR=$RPM_BUILD_ROOT + +# +# Copy in the files needed again after install +# +mkdir -p $RPM_BUILD_ROOT/opt/rt2/postinstall/bin +cp -rp Makefile etc tools $RPM_BUILD_ROOT/opt/rt2/postinstall +cp -rp bin/initacls.* $RPM_BUILD_ROOT/opt/rt2/postinstall/bin + +# logging in /var/log/rt2 +mkdir -p $RPM_BUILD_ROOT/var/log/rt2 +chown www $RPM_BUILD_ROOT/var/log/rt2 +chgrp rt $RPM_BUILD_ROOT/var/log/rt2 +chmod ug=rwx,o= $RPM_BUILD_ROOT/var/log/rt2 + +%clean +if [ x$RPM_BUILD_ROOT != x ]; then +rm -rf $RPM_BUILD_ROOT +fi + +# +# A new rt groups is required +# +%pre +groupadd rt || true + +# +# Show the user the site specific steps required after install +# +%post +cat <<EOF +----------------------------------------------------------------------- +rt2 installation is complete. Now create the rt2 database by running: +----------------------------------------------------------------------- + +# cd /opt/rt2/postinstall +# make config-replace initialize.mysql insert RT_LOG_PATH=/var/log/rt2 DB_RT_PASS=new_rt_user_password + +Choose your own new_rt_user_password. You will need the mysql root password. +You can try Pg or Oracle instead of mysql - untested. + +Review and configure your site specific details in /opt/rt2/etc/config.pm +EOF + +%preun + +%files +%dir /opt/rt2 +/opt/rt2/bin +/opt/rt2/WebRT +/opt/rt2/lib +/opt/rt2/local +/opt/rt2/man +/opt/rt2/postinstall +%dir /opt/rt2/etc +/opt/rt2/etc/insertdata +%config /opt/rt2/etc/config.pm +%dir /var/log/rt2 + +%changelog +* Mon Sep 24 2001 Jesse Vincent <jesse@bestpractical.com> + Switch to rt DESTDIR support +* Fri Sep 14 2001 Cris Bailiff <c.bailiff@devsecure.com> + Fix permissions on created /var/log/rt2 and roll in 2.0.7 +* Tue Sep 4 2001 Cris Bailiff <c.bailiff@devsecure.com> +- created initial spec file +* Tue Sep 4 2001 Cris Bailiff <c.bailiff@devsecure.com> +- created initial spec file +* Tue Sep 4 2001 Cris Bailiff <c.bailiff@devsecure.com> +- created initial spec file diff --git a/rt/etc/schema.Oracle b/rt/etc/schema.Oracle new file mode 100644 index 000000000..0c14cb39d --- /dev/null +++ b/rt/etc/schema.Oracle @@ -0,0 +1,287 @@ +CREATE SEQUENCE KEYWORDSELECTS_seq; +CREATE TABLE KeywordSelects ( + id NUMBER(11, 0) PRIMARY KEY, + Name VARCHAR2(255), + Keyword NUMBER(11, 0), + Single NUMBER(11, 0), + Depth NUMBER(11, 0) DEFAULT 0, + ObjectType VARCHAR2(32) NOT NULL, + ObjectField VARCHAR2(32), + ObjectValue VARCHAR2(255), + Disabled NUMBER(11, 0) DEFAULT 0 +); + +CREATE INDEX KeywordSelects1 ON KeywordSelects (Keyword); +CREATE INDEX KeywordSelects2 ON + KeywordSelects(ObjectType, ObjectField, ObjectValue); + + +CREATE SEQUENCE ATTACHMENTS_seq; +CREATE TABLE Attachments ( + id NUMBER(11,0) PRIMARY KEY, + TransactionId NUMBER(11,0) NOT NULL, + Parent NUMBER(11,0), + MessageId VARCHAR2(160), + Subject VARCHAR2(255), + Filename VARCHAR2(255), + ContentType VARCHAR2(80), + ContentEncoding VARCHAR2(80), + Content CLOB, + Headers CLOB, + Creator NUMBER(11,0), + Created DATE, + Disabled NUMBER(11,0) DEFAULT 0 +); + +CREATE SEQUENCE QUEUES_seq; +CREATE TABLE Queues ( + id NUMBER(11, 0) PRIMARY KEY, + Name VARCHAR2(40) NOT NULL UNIQUE, + Description VARCHAR2(120), + CorrespondAddress VARCHAR2(40), + CommentAddress VARCHAR2(40), + InitialPriority NUMBER(11, 0), + FinalPriority NUMBER(11, 0), + DefaultDueIn NUMBER(11, 0), + Creator NUMBER(11, 0), + Created DATE, + LastUpdatedBy NUMBER(11, 0), + LastUpdated DATE, + Disabled NUMBER(11,0) DEFAULT 0 +); + +CREATE SEQUENCE LINKS_seq; +CREATE TABLE Links ( + id NUMBER(11,0) PRIMARY KEY, + Base VARCHAR2(255), + Target VARCHAR2(255), + Type VARCHAR2(20) NOT NULL, + LocalTarget NUMBER(11,0), + LocalBase NUMBER(11,0), + LastUpdatedBy NUMBER(11,0), + LastUpdated DATE, + Creator NUMBER(11,0), + Created DATE +); + +CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type); + + + +CREATE SEQUENCE GROUPS_seq; +CREATE TABLE Groups ( + id NUMBER(11,0) PRIMARY KEY, + Name VARCHAR2(16) UNIQUE, + Description VARCHAR(64), + Pseudo NUMBER(11,0) DEFAULT 0 +); + +CREATE SEQUENCE WATCHERS_seq; +CREATE TABLE Watchers ( + id NUMBER(11,0) PRIMARY KEY, + Type VARCHAR2(16), + Scope VARCHAR2(16), + Value NUMBER(11,0), + Email VARCHAR2(255), + Quiet NUMBER(11,0), + Owner NUMBER(11,0), + Creator NUMBER(11,0), + Created DATE, + LastUpdatedBy NUMBER(11,0), + LastUpdated DATE +); + + + +CREATE SEQUENCE SCRIPCONDITIONS_seq; +CREATE TABLE ScripConditions ( + id NUMBER(11, 0) PRIMARY KEY, + Name VARCHAR2(255), + Description VARCHAR2(255), + ExecModule VARCHAR2(60), + Argument VARCHAR2(255), + ApplicableTransTypes VARCHAR2(60), + Creator NUMBER(11, 0), + Created DATE, + LastUpdatedBy NUMBER(11, 0), + LastUpdated DATE +); + + +CREATE SEQUENCE TRANSACTIONS_seq; +CREATE TABLE Transactions ( + id NUMBER(11,0) PRIMARY KEY, + EffectiveTicket NUMBER(11,0), + Ticket NUMBER(11,0), + TimeTaken NUMBER(11,0), + Type VARCHAR2(20), + Field VARCHAR2(40), + OldValue VARCHAR2(255), + NewValue VARCHAR2(255), + Data VARCHAR2(100), + Creator NUMBER(11,0), + Created DATE, + Disabled NUMBER(11,0) DEFAULT 0 +); + +CREATE SEQUENCE SCRIPS_seq; +CREATE TABLE Scrips ( + id NUMBER(11,0) PRIMARY KEY, + ScripCondition NUMBER(11,0), + ScripAction NUMBER(11,0), + Stage VARCHAR2(32), + Queue NUMBER(11,0), + Template NUMBER(11,0), + Creator NUMBER(11,0), + Created DATE, + LastUpdatedBy NUMBER(11,0), + LastUpdated DATE +); + + + + +CREATE SEQUENCE ACL_seq; +CREATE TABLE ACL ( + id NUMBER(11,0) PRIMARY KEY, + PrincipalId NUMBER(11,0), + PrincipalType VARCHAR2(25), + RightName VARCHAR2(25), + RightScope VARCHAR2(25), + RightAppliesTo NUMBER(11,0) +); + +CREATE SEQUENCE GROUPMEMBERS_seq; +CREATE TABLE GroupMembers ( + id NUMBER(11,0) PRIMARY KEY, + GroupId NUMBER(11,0), + UserId NUMBER(11,0) +); + +CREATE UNIQUE INDEX GroupMembers1 ON GroupMembers (GroupId, UserId); + + +CREATE SEQUENCE OBJECTKEYWORDS_seq; +CREATE TABLE ObjectKeywords ( + id NUMBER(11,0) PRIMARY KEY, + Keyword NUMBER(11,0) NOT NULL, + KeywordSelect NUMBER(11,0) NOT NULL, + ObjectType VARCHAR2(32) NOT NULL, + ObjectId NUMBER(11,0) NOT NULL +); + +CREATE UNIQUE INDEX ObjectKeywords1 ON ObjectKeywords + (ObjectId, ObjectType, KeywordSelect, Keyword); +CREATE INDEX ObjectKeywords3 ON ObjectKeywords (Keyword); + +CREATE SEQUENCE KEYWORDS_seq; +CREATE TABLE Keywords ( + id NUMBER(11, 0) PRIMARY KEY, + Name VARCHAR2(255) NOT NULL, + Description VARCHAR2(255), + Parent NUMBER(11, 0), + Disabled NUMBER(11, 0) DEFAULT 0 +); + +CREATE UNIQUE INDEX Keywords1 ON Keywords (Name, Parent); +CREATE INDEX Keywords3 ON Keywords (Parent); + +CREATE SEQUENCE USERS_seq; +CREATE TABLE Users ( + id NUMBER(11,0) PRIMARY KEY, + Name VARCHAR2(120) NOT NULL UNIQUE, + Password VARCHAR2(40), + Comments CLOB, + Signature CLOB, + EmailAddress VARCHAR2(120), + FreeFormContactInfo CLOB, + Organization VARCHAR2(200), + Privileged NUMBER(11,0), + RealName VARCHAR2(120), + NickName VARCHAR2(16), + Lang VARCHAR2(16), + EmailEncoding VARCHAR2(16), + WebEncoding VARCHAR2(16), + ExternalContactInfoId VARCHAR2(100), + ContactInfoSystem VARCHAR2(30), + ExternalAuthId VARCHAR2(100), + AuthSystem VARCHAR2(30), + Gecos VARCHAR2(16), + HomePhone VARCHAR2(30), + WorkPhone VARCHAR2(30), + MobilePhone VARCHAR2(30), + PagerPhone VARCHAR2(30), + Address1 VARCHAR2(200), + Address2 VARCHAR2(200), + City VARCHAR2(100), + State VARCHAR2(100), + Zip VARCHAR2(16), + Country VARCHAR2(50), + Creator NUMBER(11,0), + Created DATE, + LastUpdatedBy NUMBER(11,0), + LastUpdated DATE, + Disabled NUMBER(11,0) DEFAULT 0 +); + + + + +CREATE SEQUENCE TICKETS_seq; +CREATE TABLE Tickets ( + id NUMBER(11, 0) PRIMARY KEY, + EffectiveId NUMBER(11, 0), + Queue NUMBER(11,0), + Type VARCHAR2(16), + IssueStatement NUMBER(11,0), + Resolution NUMBER(11,0), + Owner NUMBER(11,0), + Subject VARCHAR2(200) DEFAULT '', + InitialPriority NUMBER(11,0) DEFAULT 0, + FinalPriority NUMBER(11,0) DEFAULT 0, + Priority NUMBER(11,0) DEFAULT 0, + Status VARCHAR2(10), + TimeWorked NUMBER(11,0) DEFAULT 0, + TimeLeft NUMBER(11,0) DEFAULT 0, + Told DATE, + Starts DATE, + Started DATE, + Due DATE, + Resolved DATE, + LastUpdatedBy NUMBER(11,0), + LastUpdated DATE, + Creator NUMBER(11,0), + Created DATE, + Disabled NUMBER(11,0) DEFAULT 0 +); + +CREATE SEQUENCE SCRIPACTIONS_seq; +CREATE TABLE ScripActions ( + id NUMBER(11,0) PRIMARY KEY, + Name VARCHAR2(255), + Description VARCHAR2(255), + ExecModule VARCHAR2(60), + Argument VARCHAR2(255), + Creator NUMBER(11,0), + Created DATE, + LastUpdatedBy NUMBER(11,0), + LastUpdated DATE +); + + +CREATE SEQUENCE TEMPLATES_seq; +CREATE TABLE Templates ( + id NUMBER(11,0) PRIMARY KEY, + Queue NUMBER(11,0) DEFAULT 0 NOT NULL, + Name VARCHAR2(40) NOT NULL UNIQUE, + Description VARCHAR2(120), + Type VARCHAR2(16), + Language VARCHAR2(16), + TranslationOf NUMBER(11,0), + Content CLOB, + LastUpdated DATE, + LastUpdatedBy NUMBER(11,0), + Creator NUMBER(11,0), + Created DATE +); + diff --git a/rt/etc/schema.Pg b/rt/etc/schema.Pg new file mode 100755 index 000000000..21d981b4a --- /dev/null +++ b/rt/etc/schema.Pg @@ -0,0 +1,267 @@ +CREATE TABLE KeywordSelects ( + id serial NOT NULL , + Name varchar(255) , + Keyword integer , + Single integer , + Depth integer NOT NULL DEFAULT 0 , + ObjectType varchar(32) NOT NULL , + ObjectField varchar(32) , + ObjectValue varchar(255) , + Disabled int2 NOT NULL DEFAULT 0 , + PRIMARY KEY (id) +); +CREATE INDEX KeywordSelects1 ON KeywordSelects (Keyword); +CREATE INDEX KeywordSelects2 ON KeywordSelects (ObjectType, ObjectField, ObjectValue); +CREATE TABLE Attachments ( + id serial NOT NULL , + TransactionId integer NOT NULL , + Parent integer , + MessageId varchar(160) , + Subject varchar(255) , + Filename varchar(255) , + ContentType varchar(80) , + ContentEncoding varchar(80) , + Content TEXT , + Headers TEXT , + Creator integer , + Created timestamp , + PRIMARY KEY (id) +); +CREATE INDEX Attachments1 ON Attachments (Parent); +CREATE INDEX Attachments2 ON Attachments (TransactionId); +CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId); +CREATE TABLE Queues ( + id serial NOT NULL , + Name varchar(120) NOT NULL , + Description varchar(120) , + CorrespondAddress varchar(120) , + CommentAddress varchar(120) , + InitialPriority integer , + FinalPriority integer , + DefaultDueIn integer , + Creator integer , + Created timestamp , + LastUpdatedBy integer , + LastUpdated timestamp , + Disabled int2 NOT NULL DEFAULT 0 , + PRIMARY KEY (id) +); +CREATE UNIQUE INDEX Queues1 ON Queues (Name); +CREATE TABLE Links ( + id serial NOT NULL , + Base varchar(240) , + Target varchar(240) , + Type varchar(20) NOT NULL , + LocalTarget integer , + LocalBase integer , + LastUpdatedBy integer , + LastUpdated timestamp , + Creator integer , + Created timestamp , + PRIMARY KEY (id) +); +CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type); +CREATE TABLE Groups ( + id serial NOT NULL , + Name varchar(16) , + Description varchar(64) , + Pseudo integer NOT NULL DEFAULT 0 , + PRIMARY KEY (id) +); +CREATE UNIQUE INDEX Groups1 ON Groups (Name); +CREATE TABLE Watchers ( + id serial NOT NULL , + Type varchar(16) , + Scope varchar(16) , + Value integer , + Email varchar(255) , + Quiet integer , + Owner integer , + Creator integer , + Created timestamp , + LastUpdatedBy integer , + LastUpdated timestamp , + PRIMARY KEY (id) +); +CREATE INDEX Watchers1 ON Watchers (Scope, Value, Type, Owner); +CREATE TABLE ScripConditions ( + id serial NOT NULL , + Name varchar(255) , + Description varchar(255) , + ExecModule varchar(60) , + Argument varchar(255) , + ApplicableTransTypes varchar(60) , + Creator integer , + Created timestamp , + LastUpdatedBy integer , + LastUpdated timestamp , + PRIMARY KEY (id) +); +CREATE TABLE Transactions ( + id serial NOT NULL , + EffectiveTicket integer , + Ticket integer , + TimeTaken integer , + Type varchar(20) , + Field varchar(40) , + OldValue varchar(255) , + NewValue varchar(255) , + Data varchar(100) , + Creator integer , + Created timestamp , + PRIMARY KEY (id) +); +CREATE INDEX Transactions1 ON Transactions (Ticket); +CREATE INDEX Transactions2 ON Transactions (EffectiveTicket); +CREATE TABLE Scrips ( + id serial NOT NULL , + ScripCondition integer , + ScripAction integer , + Stage varchar(32) , + Queue integer , + Template integer , + Creator integer , + Created timestamp , + LastUpdatedBy integer , + LastUpdated timestamp , + PRIMARY KEY (id) +); +CREATE TABLE ACL ( + id serial NOT NULL , + PrincipalId integer , + PrincipalType varchar(25) , + RightName varchar(25) , + RightScope varchar(25) , + RightAppliesTo integer , + PRIMARY KEY (id) +); +CREATE INDEX ACL1 ON ACL (RightScope, PrincipalId); +CREATE INDEX ACL2 ON ACL (RightScope, RightAppliesTo, RightName, PrincipalType, PrincipalId); +CREATE TABLE GroupMembers ( + id serial NOT NULL , + GroupId integer , + UserId integer , + PRIMARY KEY (id) +); +CREATE UNIQUE INDEX GroupMembers1 ON GroupMembers (GroupId, UserId); +CREATE TABLE ObjectKeywords ( + id serial NOT NULL , + Keyword integer NOT NULL , + KeywordSelect integer NOT NULL , + ObjectType varchar(32) NOT NULL , + ObjectId integer NOT NULL , + PRIMARY KEY (id) +); +CREATE UNIQUE INDEX ObjectKeywords1 ON ObjectKeywords (ObjectId, ObjectType, KeywordSelect, Keyword); +CREATE INDEX ObjectKeywords2 ON ObjectKeywords (ObjectId, ObjectType); +CREATE INDEX ObjectKeywords3 ON ObjectKeywords (Keyword); +CREATE TABLE Keywords ( + id serial NOT NULL , + Name varchar(255) NOT NULL , + Description varchar(255) , + Parent integer , + Disabled int2 NOT NULL DEFAULT 0 , + PRIMARY KEY (id) +); +CREATE UNIQUE INDEX Keywords1 ON Keywords (Name, Parent); +CREATE INDEX Keywords2 ON Keywords (Name); +CREATE INDEX Keywords3 ON Keywords (Parent); +CREATE TABLE Users ( + id serial NOT NULL , + Name varchar(120) NOT NULL , + Password varchar(40) , + Comments TEXT , + Signature TEXT , + EmailAddress varchar(120) , + FreeformContactInfo TEXT , + Organization varchar(200) , + Privileged integer , + RealName varchar(120) , + Nickname varchar(16) , + Lang varchar(16) , + EmailEncoding varchar(16) , + WebEncoding varchar(16) , + ExternalContactInfoId varchar(100) , + ContactInfoSystem varchar(30) , + ExternalAuthId varchar(100) , + AuthSystem varchar(30) , + Gecos varchar(16) , + HomePhone varchar(30) , + WorkPhone varchar(30) , + MobilePhone varchar(30) , + PagerPhone varchar(30) , + Address1 varchar(200) , + Address2 varchar(200) , + City varchar(100) , + State varchar(100) , + Zip varchar(16) , + Country varchar(50) , + Creator integer , + Created timestamp , + LastUpdatedBy integer , + LastUpdated timestamp , + Disabled int2 NOT NULL DEFAULT 0 , + PRIMARY KEY (id) +); +CREATE UNIQUE INDEX Users1 ON Users (Name); +CREATE INDEX Users3 ON Users (id, EmailAddress); +CREATE INDEX Users4 ON Users (EmailAddress); +CREATE TABLE Tickets ( + id serial NOT NULL , + EffectiveId integer , + Queue integer , + Type varchar(16) , + IssueStatement integer , + Resolution integer , + Owner integer , + Subject varchar(200) DEFAULT '[no subject]' , + InitialPriority integer , + FinalPriority integer , + Priority integer , + Status varchar(10) , + TimeWorked integer , + TimeLeft integer , + Told timestamp , + Starts timestamp , + Started timestamp , + Due timestamp , + Resolved timestamp , + LastUpdatedBy integer , + LastUpdated timestamp , + Creator integer , + Created timestamp , + Disabled int2 NOT NULL DEFAULT 0 , + PRIMARY KEY (id) +); +CREATE INDEX Tickets1 ON Tickets (Queue, Status); +CREATE INDEX Tickets2 ON Tickets (Owner); +CREATE INDEX Tickets3 ON Tickets (EffectiveId); +CREATE INDEX Tickets4 ON Tickets (id, Status); +CREATE INDEX Tickets5 ON Tickets (id, EffectiveId); +CREATE TABLE ScripActions ( + id serial NOT NULL , + Name varchar(255) , + Description varchar(255) , + ExecModule varchar(60) , + Argument varchar(255) , + Creator integer , + Created timestamp , + LastUpdatedBy integer , + LastUpdated timestamp , + PRIMARY KEY (id) +); +CREATE TABLE Templates ( + id serial NOT NULL , + Queue integer NOT NULL DEFAULT 0 , + Name varchar(40) NOT NULL , + Description varchar(120) , + Type varchar(16) , + Language varchar(16) , + TranslationOf integer , + Content TEXT , + LastUpdated timestamp , + LastUpdatedBy integer , + Creator integer , + Created timestamp , + PRIMARY KEY (id) +); diff --git a/rt/etc/schema.mysql b/rt/etc/schema.mysql new file mode 100755 index 000000000..7e715c201 --- /dev/null +++ b/rt/etc/schema.mysql @@ -0,0 +1,267 @@ +CREATE TABLE KeywordSelects ( + id INTEGER NOT NULL AUTO_INCREMENT, + Name varchar(255) NULL , + Keyword integer NULL , + Single integer NULL , + Depth integer NOT NULL DEFAULT 0 , + ObjectType varchar(32) NOT NULL , + ObjectField varchar(32) NULL , + ObjectValue varchar(255) NULL , + Disabled int2 NOT NULL DEFAULT 0 , + PRIMARY KEY (id) +); +CREATE INDEX KeywordSelects1 ON KeywordSelects (Keyword); +CREATE INDEX KeywordSelects2 ON KeywordSelects (ObjectType, ObjectField, ObjectValue); +CREATE TABLE Attachments ( + id INTEGER NOT NULL AUTO_INCREMENT, + TransactionId integer NOT NULL , + Parent integer NULL , + 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 NULL , + Created DATETIME NULL , + PRIMARY KEY (id) +); +CREATE INDEX Attachments1 ON Attachments (Parent); +CREATE INDEX Attachments2 ON Attachments (TransactionId); +CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId); +CREATE TABLE Queues ( + id INTEGER NOT NULL AUTO_INCREMENT, + Name varchar(120) NOT NULL , + Description varchar(120) NULL , + CorrespondAddress varchar(120) NULL , + CommentAddress varchar(120) NULL , + InitialPriority integer NULL , + FinalPriority integer NULL , + DefaultDueIn integer NULL , + Creator integer NULL , + Created DATETIME NULL , + LastUpdatedBy integer NULL , + LastUpdated DATETIME NULL , + Disabled int2 NOT NULL DEFAULT 0 , + PRIMARY KEY (id) +); +CREATE UNIQUE INDEX Queues1 ON Queues (Name); +CREATE TABLE Links ( + id INTEGER NOT NULL AUTO_INCREMENT, + Base varchar(240) NULL , + Target varchar(240) NULL , + Type varchar(20) NOT NULL , + LocalTarget integer NULL , + LocalBase integer NULL , + LastUpdatedBy integer NULL , + LastUpdated DATETIME NULL , + Creator integer NULL , + Created DATETIME NULL , + PRIMARY KEY (id) +); +CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type); +CREATE TABLE Groups ( + id INTEGER NOT NULL AUTO_INCREMENT, + Name varchar(16) NULL , + Description varchar(64) NULL , + Pseudo integer NOT NULL DEFAULT 0 , + PRIMARY KEY (id) +); +CREATE UNIQUE INDEX Groups1 ON Groups (Name); +CREATE TABLE Watchers ( + id INTEGER NOT NULL AUTO_INCREMENT, + Type varchar(16) NULL , + Scope varchar(16) NULL , + Value integer NULL , + Email varchar(255) NULL , + Quiet integer NULL , + Owner integer NULL , + Creator integer NULL , + Created DATETIME NULL , + LastUpdatedBy integer NULL , + LastUpdated DATETIME NULL , + PRIMARY KEY (id) +); +CREATE INDEX Watchers1 ON Watchers (Scope, Value, Type, Owner); +CREATE TABLE ScripConditions ( + id INTEGER NOT NULL AUTO_INCREMENT, + Name varchar(255) NULL , + Description varchar(255) NULL , + ExecModule varchar(60) NULL , + Argument varchar(255) NULL , + ApplicableTransTypes varchar(60) NULL , + Creator integer NULL , + Created DATETIME NULL , + LastUpdatedBy integer NULL , + LastUpdated DATETIME NULL , + PRIMARY KEY (id) +); +CREATE TABLE Transactions ( + id INTEGER NOT NULL AUTO_INCREMENT, + EffectiveTicket integer NULL , + Ticket integer NULL , + TimeTaken integer NULL , + Type varchar(20) NULL , + Field varchar(40) NULL , + OldValue varchar(255) NULL , + NewValue varchar(255) NULL , + Data varchar(100) NULL , + Creator integer NULL , + Created DATETIME NULL , + PRIMARY KEY (id) +); +CREATE INDEX Transactions1 ON Transactions (Ticket); +CREATE INDEX Transactions2 ON Transactions (EffectiveTicket); +CREATE TABLE Scrips ( + id INTEGER NOT NULL AUTO_INCREMENT, + ScripCondition integer NULL , + ScripAction integer NULL , + Stage varchar(32) NULL , + Queue integer NULL , + Template integer NULL , + Creator integer NULL , + Created DATETIME NULL , + LastUpdatedBy integer NULL , + LastUpdated DATETIME NULL , + PRIMARY KEY (id) +); +CREATE TABLE ACL ( + id INTEGER NOT NULL AUTO_INCREMENT, + PrincipalId integer NULL , + PrincipalType varchar(25) NULL , + RightName varchar(25) NULL , + RightScope varchar(25) NULL , + RightAppliesTo integer NULL , + PRIMARY KEY (id) +); +CREATE INDEX ACL1 ON ACL (RightScope, PrincipalId); +CREATE INDEX ACL2 ON ACL (RightScope, RightAppliesTo, RightName, PrincipalType, PrincipalId); +CREATE TABLE GroupMembers ( + id INTEGER NOT NULL AUTO_INCREMENT, + GroupId integer NULL , + UserId integer NULL , + PRIMARY KEY (id) +); +CREATE UNIQUE INDEX GroupMembers1 ON GroupMembers (GroupId, UserId); +CREATE TABLE ObjectKeywords ( + id INTEGER NOT NULL AUTO_INCREMENT, + Keyword integer NOT NULL , + KeywordSelect integer NOT NULL , + ObjectType varchar(32) NOT NULL , + ObjectId integer NOT NULL , + PRIMARY KEY (id) +); +CREATE UNIQUE INDEX ObjectKeywords1 ON ObjectKeywords (ObjectId, ObjectType, KeywordSelect, Keyword); +CREATE INDEX ObjectKeywords2 ON ObjectKeywords (ObjectId, ObjectType); +CREATE INDEX ObjectKeywords3 ON ObjectKeywords (Keyword); +CREATE TABLE Keywords ( + id INTEGER NOT NULL AUTO_INCREMENT, + Name varchar(255) NOT NULL , + Description varchar(255) NULL , + Parent integer NULL , + Disabled int2 NOT NULL DEFAULT 0 , + PRIMARY KEY (id) +); +CREATE UNIQUE INDEX Keywords1 ON Keywords (Name, Parent); +CREATE INDEX Keywords2 ON Keywords (Name); +CREATE INDEX Keywords3 ON Keywords (Parent); +CREATE TABLE Users ( + id INTEGER NOT NULL AUTO_INCREMENT, + Name varchar(120) NOT NULL , + Password varchar(40) NULL , + Comments blob NULL , + Signature blob NULL , + EmailAddress varchar(120) NULL , + FreeformContactInfo blob NULL , + Organization varchar(200) NULL , + Privileged integer 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 , + Creator integer NULL , + Created DATETIME NULL , + LastUpdatedBy integer NULL , + LastUpdated DATETIME NULL , + Disabled int2 NOT NULL DEFAULT 0 , + PRIMARY KEY (id) +); +CREATE UNIQUE INDEX Users1 ON Users (Name); +CREATE INDEX Users3 ON Users (id, EmailAddress); +CREATE INDEX Users4 ON Users (EmailAddress); +CREATE TABLE Tickets ( + id INTEGER NOT NULL AUTO_INCREMENT, + EffectiveId integer NULL , + Queue integer NULL , + Type varchar(16) NULL , + IssueStatement integer NULL , + Resolution integer NULL , + Owner integer NULL , + Subject varchar(200) NULL DEFAULT '[no subject]' , + InitialPriority integer NULL , + FinalPriority integer NULL , + Priority integer NULL , + Status varchar(10) NULL , + TimeWorked integer NULL , + TimeLeft integer NULL , + Told DATETIME NULL , + Starts DATETIME NULL , + Started DATETIME NULL , + Due DATETIME NULL , + Resolved DATETIME NULL , + LastUpdatedBy integer NULL , + LastUpdated DATETIME NULL , + Creator integer NULL , + Created DATETIME NULL , + Disabled int2 NOT NULL DEFAULT 0 , + PRIMARY KEY (id) +); +CREATE INDEX Tickets1 ON Tickets (Queue, Status); +CREATE INDEX Tickets2 ON Tickets (Owner); +CREATE INDEX Tickets3 ON Tickets (EffectiveId); +CREATE INDEX Tickets4 ON Tickets (id, Status); +CREATE INDEX Tickets5 ON Tickets (id, EffectiveId); +CREATE TABLE ScripActions ( + id INTEGER NOT NULL AUTO_INCREMENT, + Name varchar(255) NULL , + Description varchar(255) NULL , + ExecModule varchar(60) NULL , + Argument varchar(255) NULL , + Creator integer NULL , + Created DATETIME NULL , + LastUpdatedBy integer NULL , + LastUpdated DATETIME NULL , + PRIMARY KEY (id) +); +CREATE TABLE Templates ( + id INTEGER NOT NULL AUTO_INCREMENT, + Queue integer NOT NULL DEFAULT 0 , + Name varchar(40) NOT NULL , + Description varchar(120) NULL , + Type varchar(16) NULL , + Language varchar(16) NULL , + TranslationOf integer NULL , + Content blob NULL , + LastUpdated DATETIME NULL , + LastUpdatedBy integer NULL , + Creator integer NULL , + Created DATETIME NULL , + PRIMARY KEY (id) +); diff --git a/rt/etc/schema.pm b/rt/etc/schema.pm new file mode 100644 index 000000000..44e143ecd --- /dev/null +++ b/rt/etc/schema.pm @@ -0,0 +1,349 @@ +# column, type, nullability, length, default, database-local + +my $gratuitous = { + +'Groups' => { + 'columns' => [ + 'id', 'serial', '', '', '', '', + 'Name', 'varchar', 'NULL', '16', '', '', + 'Description', 'varchar', 'NULL', '64', '', '', + 'Pseudo', 'integer', '', '', '0', '', + ], + 'primary_key' => 'id', + 'unique' => [ ['Name'] ], + 'index' => [ ], +}, + +'ACL' => { + 'columns' => [ + 'id', 'serial', '', '', '', '', + 'PrincipalId', 'integer', 'NULL', '', '', '', + 'PrincipalType', 'varchar', 'NULL', '25', '', '', + 'RightName', 'varchar', 'NULL', '25', '', '', + 'RightScope', 'varchar', 'NULL', '25', '', '', + 'RightAppliesTo', 'integer', 'NULL', '', '', '', + ], + 'primary_key' => 'id', + 'unique' => [ ], + 'index' => [ ['RightScope', 'PrincipalId'], + ['RightScope','RightAppliesTo','RightName','PrincipalType','PrincipalId'] ], +}, + +'Watchers' => { + 'columns' => [ + 'id', 'serial', '', '', '', '', + 'Type', 'varchar', 'NULL', '16', '', '', + 'Scope', 'varchar', 'NULL', '16', '', '', + 'Value', 'integer', 'NULL', '', '', '', + 'Email', 'varchar', 'NULL', '255', '', '', + 'Quiet', 'integer', 'NULL', '', '', '', + 'Owner', 'integer', 'NULL', '', '', '', + 'Creator', 'integer', 'NULL', '', '', '', + 'Created', 'timestamp', 'NULL', '', '', '', + 'LastUpdatedBy', 'integer', 'NULL', '', '', '', + 'LastUpdated', 'timestamp', 'NULL', '', '', '', + ], + 'primary_key' => 'id', + 'unique' => [ ], + 'index' => [ ['Scope','Value','Type','Owner'] ], +}, + +'Links' => { + 'columns' => [ + 'id', 'serial', '', '', '', '', + 'Base', 'varchar', 'NULL', '240', '', '', + 'Target', 'varchar', 'NULL', '240', '', '', + 'Type', 'varchar', '', '20', '', '', + 'LocalTarget', 'integer', 'NULL', '', '', '', + 'LocalBase', 'integer', 'NULL', '', '', '', + 'LastUpdatedBy', 'integer', 'NULL', '', '', '', + 'LastUpdated', 'timestamp', 'NULL', '', '', '', + 'Creator', 'integer', 'NULL', '', '', '', + 'Created', 'timestamp', 'NULL', '', '', '', + ], + 'primary_key' => 'id', + 'unique' => [ ['Base', 'Target', 'Type'] ], + 'index' => [ ], +}, + +'Users' => { + 'columns' => [ + 'id', 'serial', '', '', '', '', + 'Name', 'varchar', '', '120', '', '', + 'Password', 'varchar', 'NULL', '40', '', '', + 'Comments', 'blob', 'NULL', '', '', '', + 'Signature', 'blob', 'NULL', '', '', '', + 'EmailAddress', 'varchar', 'NULL', '120', '', '', + 'FreeformContactInfo', 'blob', 'NULL', '', '', '', + 'Organization', 'varchar', 'NULL', '200', '', '', + 'Privileged', 'integer', 'NULL', '', '', '', + 'RealName', 'varchar', 'NULL', '120', '', '', + 'Nickname', 'varchar', 'NULL', '16', '', '', + 'Lang', 'varchar', 'NULL', '16', '', '', + 'EmailEncoding', 'varchar', 'NULL', '16', '', '', + 'WebEncoding', 'varchar', 'NULL', '16', '', '', + 'ExternalContactInfoId', 'varchar', 'NULL', '100', '', '', + 'ContactInfoSystem', 'varchar', 'NULL', '30', '', '', + 'ExternalAuthId', 'varchar', 'NULL', '100', '', '', + 'AuthSystem', 'varchar', 'NULL', '30', '', '', + 'Gecos', 'varchar', 'NULL', '16', '', '', + 'HomePhone', 'varchar', 'NULL', '30', '', '', + 'WorkPhone', 'varchar', 'NULL', '30', '', '', + 'MobilePhone', 'varchar', 'NULL', '30', '', '', + 'PagerPhone', 'varchar', 'NULL', '30', '', '', + 'Address1', 'varchar', 'NULL', '200', '', '', + 'Address2', 'varchar', 'NULL', '200', '', '', + 'City', 'varchar', 'NULL', '100', '', '', + 'State', 'varchar', 'NULL', '100', '', '', + 'Zip', 'varchar', 'NULL', '16', '', '', + 'Country', 'varchar', 'NULL', '50', '', '', + 'Creator', 'integer', 'NULL', '', '', '', + 'Created', 'timestamp', 'NULL', '', '', '', + 'LastUpdatedBy', 'integer', 'NULL', '', '', '', + 'LastUpdated', 'timestamp', 'NULL', '', '', '', + 'Disabled', 'int2', '','','0','', + ], + 'primary_key' => 'id', + 'unique' => [ ['Name'] ], + 'index' => [ ['Name'], + ['id', 'EmailAddress'], + ['EmailAddress'] ], +}, + +'Tickets' => { + 'columns' => [ + 'id', 'serial', '', '', '', '', + 'EffectiveId', 'integer', 'NULL', '', '', '', + 'Queue', 'integer', 'NULL', '', '', '', + 'Type', 'varchar', 'NULL', '16', '', '', + 'IssueStatement', 'integer', 'NULL', '', '', '', + 'Resolution', 'integer', 'NULL', '', '', '', + 'Owner', 'integer', 'NULL', '', '', '', + 'Subject', 'varchar', 'NULL', '200', '[no subject]', '', + 'InitialPriority', 'integer', 'NULL', '', '', '', + 'FinalPriority', 'integer', 'NULL', '', '', '', + 'Priority', 'integer', 'NULL', '', '', '', + 'Status', 'varchar', 'NULL', '10', '', '', + 'TimeWorked', 'integer', 'NULL', '', '', '', + 'TimeLeft', 'integer', 'NULL', '', '', '', + 'Told', 'timestamp', 'NULL', '', '', '', + 'Starts', 'timestamp', 'NULL', '', '', '', + 'Started', 'timestamp', 'NULL', '', '', '', + 'Due', 'timestamp', 'NULL', '', '', '', + 'Resolved', 'timestamp', 'NULL', '', '', '', + 'LastUpdatedBy', 'integer', 'NULL', '', '', '', + 'LastUpdated', 'timestamp', 'NULL', '', '', '', + 'Creator', 'integer', 'NULL', '', '', '', + 'Created', 'timestamp', 'NULL', '', '', '', + 'Disabled', 'int2', '','','0','', + ], + 'primary_key' => 'id', + 'unique' => [ [] ], + 'index' => [ ['Queue', 'Status'], + ['Owner'], + ['EffectiveId'], + ['id', 'Status'], + ['id', 'EffectiveId'] ], +}, + +'GroupMembers' => { + 'columns' => [ + 'id', 'serial', '', '', '', '', + 'GroupId', 'integer', 'NULL', '', '', '', #foreign key, Groups::id + 'UserId', 'integer', 'NULL', '', '', '', #foreign key, Users::id + ], + 'primary_key' => 'id', + 'unique' => [ ['GroupId', 'UserId'] ], + 'index' => [ ], +}, + +'Queues' => { + 'columns' => [ + 'id', 'serial', '', '', '', '', + 'Name', 'varchar', '', '120', '', '', #Textual 'name' for this queue + 'Description', 'varchar', 'NULL', '120', '', '', #Textual descr. of this + #queue + 'CorrespondAddress', 'varchar', 'NULL', '120', '', '', + 'CommentAddress', 'varchar', 'NULL', '120', '', '', + 'InitialPriority', 'integer', 'NULL', '', '', '', + 'FinalPriority', 'integer', 'NULL', '', '', '', + 'DefaultDueIn', 'integer', 'NULL', '', '', '', + + 'Creator', 'integer', 'NULL', '', '', '', + 'Created', 'timestamp', 'NULL', '', '', '', + 'LastUpdatedBy', 'integer', 'NULL', '', '', '', + 'LastUpdated', 'timestamp', 'NULL', '', '', '', + 'Disabled', 'int2', '','','0','', + ], + 'primary_key' => 'id', + 'unique' => [ ['Name'] ], + 'index' => [ ], +}, + +'Transactions' => { + 'columns' => [ + 'id', 'serial', '', '', '', '', + 'EffectiveTicket', 'integer', 'NULL', '', '', '', + 'Ticket', 'integer', 'NULL', '', '', '', #Foreign key Ticket::id + 'TimeTaken', 'integer', 'NULL', '', '', '', #Time spent on this trans in min + 'Type', 'varchar', 'NULL', '20', '', '', + 'Field', 'varchar', 'NULL', '40', '', '', #If it's a "Set" transaction, what + #field was set. + 'OldValue', 'varchar', 'NULL', '255', '', '', + 'NewValue', 'varchar', 'NULL', '255', '', '', + 'Data', 'varchar', 'NULL', '100', '', '', + + + 'Creator', 'integer', 'NULL', '', '', '', + 'Created', 'timestamp', 'NULL', '', '', '', + + ], + 'primary_key' => 'id', + 'unique' => [ ], + 'index' => [ ['Ticket'], ['EffectiveTicket'] ], +}, + +'ScripActions' => { + 'columns' => [ + 'id', 'serial', '', '', '', '', + 'Name', 'varchar', 'NULL', '255', '', '', # Alias + 'Description', 'varchar', 'NULL', '255', '', '', #Textual description + 'ExecModule', 'varchar', 'NULL', '60', '', '', #This calles RT::Action::___ + 'Argument', 'varchar', 'NULL', '255', '', '', #We can pass a single argument + #to the scrip. sometimes, it's who to send mail to. + 'Creator', 'integer', 'NULL', '', '', '', + 'Created', 'timestamp', 'NULL', '', '', '', + 'LastUpdatedBy', 'integer', 'NULL', '', '', '', + 'LastUpdated', 'timestamp', 'NULL', '', '', '', + ], + 'primary_key' => 'id', + 'unique' => [ ], + 'index' => [ ], +}, + +'ScripConditions' => { + 'columns' => [ + 'id', 'serial', '', '', '', '', + 'Name', 'varchar', 'NULL', '255', '', '', # Alias + 'Description', 'varchar', 'NULL', '255', '', '', #Textual description + 'ExecModule', 'varchar', 'NULL', '60', '', '', #This calles RT::Condition:: + 'Argument', 'varchar', 'NULL', '255', '', '', #We can pass a single argument + #to the scrip. sometimes, it's who to send mail to. + 'ApplicableTransTypes', 'varchar', 'NULL', '60', '', '',#Transaction types this scrip + # acts on. comma or / delimited is just great. + 'Creator', 'integer', 'NULL', '', '', '', + 'Created', 'timestamp', 'NULL', '', '', '', + 'LastUpdatedBy', 'integer', 'NULL', '', '', '', + 'LastUpdated', 'timestamp', 'NULL', '', '', '', + ], + 'primary_key' => 'id', + 'unique' => [ ], + 'index' => [ ], +}, +'Scrips' => { + 'columns' => [ + 'id', 'serial', '', '', '', '', + 'ScripCondition', 'integer', 'NULL', '', '', '', #Foreign key ScripConditions::id + 'ScripAction', 'integer', 'NULL', '', '', '', #Foreign key ScripActions::id + 'Stage', 'varchar', 'NULL', '32','','', #What stage does this scrip + #Happen in. for now, everything is 'TransactionCreate', + 'Queue', 'integer', 'NULL', '', '', '', #Foreign key Queues::id + 'Template', 'integer', 'NULL', '', '', '', #Foreign key Templates::id + + 'Creator', 'integer', 'NULL', '', '', '', + 'Created', 'timestamp', 'NULL', '', '', '', + 'LastUpdatedBy', 'integer', 'NULL', '', '', '', + 'LastUpdated', 'timestamp', 'NULL', '', '', '', + ], + 'primary_key' => 'id', + 'unique' => [ ], + 'index' => [ ], +}, + +'Attachments' => { + 'columns' => [ + 'id', 'serial', '', '', '', '', + 'TransactionId', 'integer', '', '', '', '', #Foreign key Transactions::Id + 'Parent', 'integer', 'NULL', '', '', '', # Attachments::Id + 'MessageId', 'varchar', 'NULL', '160', '', '', #RFC822 messageid, if any + 'Subject', 'varchar', 'NULL', '255', '', '', + 'Filename', 'varchar', 'NULL', '255', '', '', + 'ContentType', 'varchar', 'NULL', '80', '', '', + 'ContentEncoding', 'varchar', 'NULL', '80', '', '', + 'Content', 'long varbinary', 'NULL', '', '', '', + 'Headers', 'long varbinary', 'NULL', '', '', '', + + 'Creator', 'integer', 'NULL', '', '', '', + 'Created', 'timestamp', 'NULL', '', '', '', + + ], + 'primary_key' => 'id', + 'unique' => [ ], + 'index' => [ ['Parent'], ['TransactionId'], ['Parent', 'TransactionId'] ], +}, + +'Templates' => { + 'columns' => [ + 'id', 'serial', '', '', '', '', + 'Queue', 'integer', 'NOT NULL', '', '0', '', + 'Name', 'varchar', '', '40', '', '', + 'Description', 'varchar', 'NULL', '120', '', '', + 'Type', 'varchar', 'NULL', '16', '','', + 'Language', 'varchar', 'NULL', '16', '', '', + 'TranslationOf', 'integer', 'NULL', '', '', '', + 'Content', 'blob', 'NULL', '', '', '', + 'LastUpdated', 'timestamp', 'NULL', '', '', '', + 'LastUpdatedBy', 'integer', 'NULL', '', '', '', + 'Creator', 'integer', 'NULL', '', '', '', + 'Created', 'timestamp', 'NULL', '', '', '', + ], + 'primary_key' => 'id', + 'unique' => [ [''] ], + 'index' => [ ], +}, + +'Keywords' => { + 'columns' => [ + 'id', 'serial', '', '', '', '', + 'Name', 'varchar', 'NOT NULL', '255', '', '', + 'Description', 'varchar', 'NULL', '255', '', '', + 'Parent', 'integer', 'NULL', '', '', '', + 'Disabled', 'int2', '','','0','', +], + 'primary_key' => 'id', + 'unique' => [ [ 'Name', 'Parent' ] ], + 'index' => [ [ 'Name', ], [ 'Parent' ] ], +}, + +'ObjectKeywords' => { + 'columns' => [ + 'id', 'serial', '', '', '', '', + 'Keyword', 'integer', 'NOT NULL', '', '', '', + 'KeywordSelect', 'integer', 'NOT NULL', '', '', '', + 'ObjectType', 'varchar', 'NOT NULL', '32', '', '', + 'ObjectId', 'integer', 'NOT NULL', '', '', '', + ], + 'primary_key' => 'id', + 'unique' => [ [ 'ObjectId', 'ObjectType','KeywordSelect', 'Keyword' ] ], + 'index' => [ [ 'ObjectId', 'ObjectType' ] , ['Keyword'] ], + +}, + +'KeywordSelects' => { + 'columns' => [ + 'id', 'serial', '', '', '', '', + 'Name','varchar','NULL','255','','', + 'Keyword', 'integer', 'NULL', '', '', '', + 'Single', 'integer', 'NULL', '', '', '', + 'Depth', 'integer', 'NOT NULL', '', 0, '', + 'ObjectType', 'varchar', 'NOT NULL', '32', '', '', + 'ObjectField', 'varchar', 'NULL', '32', '', '', + 'ObjectValue', 'varchar', 'NULL', '255', '', '', + 'Disabled', 'int2', '','','0','', + ], + 'primary_key' => 'id', + 'unique' => [ [ ] ], + 'index' => [ [ 'Keyword' ], [ 'ObjectType', 'ObjectField', 'ObjectValue'] ], +}, + +}; |