SQL

• By inspection, he thinks my SQL for safely accessing RT data will be fine.
  • He says no issue with us seeing all item titles, and probably no issue with us seeing the queue all items are in (the second is more restricted according to the RT data model).
  • TO BE DETERMINED: will a mysql view, grant access to the necessary bits, but not allow access to the other data we shouldn't have access to?
    • Looks like: yes! http://dba.stackexchange.com/questions/86802/grant-permissions-on-views-deny-select-on-tables

As we've discussed, Lawrence wants to get CS subscription and time-spent data 
for all tickets including those which we started but were handed outside of CS's 
visibility (eg., to security).
ST's code is quite different from RT's in this respect since this involves custom fields, 
so I installed RT on an ubuntu system and did a bunch of testing. For easier installing 
via ubuntu package, I did the below on RT 4.2 but I've confirmed the schema is identical 
for the columns we're interested in in 4.4.

Here's my proposed plan: 
1) a new database user "cs-rt" receives access to the following columns in RT's 
  "Transactions" table: ObjectId, Created, ObjectType, TimeTaken
2) a new database view is created, visible to user "cs-rt", which returns key/value 
  of ticket and subscription-code, for all RT queues.

CREATE VIEW CsSubscriptions AS
  SELECT DISTINCT ObjectCustomFieldValues.ObjectId, ObjectCustomFieldValues.content as SubscriptionCode
  FROM ObjectCustomFieldValues
  INNER JOIN CustomFields
    ON ObjectCustomFieldValues.CustomField = CustomFields.id
  WHERE
    ObjectCustomFieldValues.ObjectType = "RT::Ticket"
    AND CustomFields.Disabled = 0
    AND ObjectCustomFieldValues.Disabled = 0
    AND CustomFields.Name = "CSSubscription"

Then I believe my code just needs to run a query similar to:

select Transactions.TimeTaken, CsSubscriptions.SubscriptionCode from
Transactions, CsSubscriptions
WHERE
Transactions.ObjectType = 'RT::Ticket'
AND Transactions.ObjectId =  CsSubscriptions.ObjectId
AND Transactions.Created > '2017-02-01 15:22:34'
AND transactions.Created < '2017-01-01 00:00:00' 

Remarks:
- I wrote this to be as security-restrictive as possible. 
- this is a fix for the first of two apps Lawrence runs (the subscriptions app). I haven't 
fully spec'd out the needs for the second app (ST-time-tracking) but it uses the above 
data, and also Transactions.Creator, some fields from Tickets table: (id, Queue, Status, 
Subject) that I want to test before I give you a suggested plan. But I wanted to let you 
know there would be another request later. (Hm, I guess it would also need Queues.Name 
and Queues.id - so maybe for simplicity that should also be a view). 

Validating user-input when supplying DNS data

• for 'hostname' field: validate entry and warn if value doesn't resolve in DNS.
• Daniel to look up docs for validation callback- check update.html under tickets

Using a supplied custom value to do API lookup

• for barcode field: do an API lookup on our inventory system, pull back room number and possibly other values, and display that as a custom-values result.
• Daniel to look up docs: STToRTBestPracticalTraining20170118-19 end of day two: "pulling values from external db".
• Jeff says we could store a standalone CGI on their server, to support cross-loading the data from within RT.

How does Jeff want to receive our code?

• Jeff says there is good docs on how to create plugins; we can ship around the plugin code that way.
• Daniel offered to make a git repo rooted at the top of RT; probably not necessary for Jeff's purposes.

-- DanielAllen - 2017-03-08