Ticket #647: RT To Trac.sql

--RT MySQL Database

--Tickets
SELECT CONCAT('INSERT INTO ticket (id, component, time, priority, owner, reporter, summary) VALUES ('
, Tickets.id,
', ''',
Queues.Name,
''', ',
'EXTRACT(EPOCH FROM ''',
Tickets.Created, '''::timestamp), ',
Tickets.Priority,
', ''',
Owners.Name,
''', ''',
COALESCE((SELECT Content FROM ObjectCustomFieldValues Requestors WHERE Tickets.id = Requestors.ObjectId AND Requestors.CustomField = 1 LIMIT 1), ''),
''', ''',
REPLACE(Tickets.Subject, '''', ''''''),
''');'
)
FROM Tickets
INNER JOIN Users Owners ON Tickets.Owner = Owners.Id
INNER JOIN Queues ON Queues.id = Tickets.Queue
WHERE Tickets.id > 1
ORDER BY Tickets.id
INTO OUTFILE 'Tickets.sql';

--Status
SELECT CONCAT('INSERT INTO ticket_custom (ticket, name, value) VALUES (',
Tickets.id,
', ''current_status'', ''',
Tickets.Status,
''');')
FROM Tickets
INTO OUTFILE 'Status.sql';

--Effort
SELECT CONCAT('INSERT INTO ticket_custom (ticket, name, value) VALUES (',
Tickets.id,
', ''development_effort'', ''',
MIN(ObjectCustomFieldValues.Content),
''');')
FROM Tickets
INNER JOIN ObjectCustomFieldValues ON ObjectCustomFieldValues.ObjectId = Tickets.id
AND ObjectCustomFieldValues.CustomField = 2
GROUP BY Tickets.id
ORDER BY Tickets.id
INTO OUTFILE 'Effort.sql';

--Descriptions
SELECT CONCAT('UPDATE ticket SET description = REPLACE(''',
REPLACE(REPLACE(Attachments.Content, '''', ''''''), '>', ''),
''', ''\\'', '''') WHERE id = ',
Tickets.id,
';')
FROM Tickets
INNER JOIN Transactions ON Transactions.ObjectId = Tickets.id AND Transactions.Type = 'Create'
INNER JOIN Attachments ON Attachments.TransactionId = Transactions.id AND Attachments.ContentType = 'text/plain' AND Attachments.Content <> '' AND Attachments.Content IS NOT NULL
INTO OUTFILE 'Descriptions.sql';

--Comments
SELECT CONCAT('INSERT INTO ticket_change (ticket, time, field, newvalue, author) VALUES (',
Tickets.id,
', EXTRACT(EPOCH FROM ''',
Transactions.Created, '''::timestamp)',
', ''comment'', REPLACE(''',
REPLACE(REPLACE(Attachments.Content, '''', ''''''), '>', ''),
''', ''\\'', ''''), ''',
COALESCE(author.Name, ''),
''');')
FROM Tickets
INNER JOIN Transactions ON Transactions.ObjectId = Tickets.id AND Transactions.Type = 'Comment'
INNER JOIN Attachments ON Attachments.TransactionId = Transactions.id AND Attachments.ContentType = 'text/plain' AND Attachments.Content <> '' AND Attachments.Content IS NOT NULL
INNER JOIN Users author ON author.id = Transactions.Creator
INTO OUTFILE 'Comments.sql';

--Change Owner
SELECT CONCAT('INSERT INTO ticket_change (ticket, time, field, oldvalue, newvalue, author) VALUES (',
Tickets.id,
', EXTRACT(EPOCH FROM ''',
Transactions.Created, '''::timestamp)',
', ''owner'', ''',
COALESCE(old.Name, ''),
''', ''',
COALESCE(new.Name, ''),
''', ''',
COALESCE(author.Name, ''),
''');')
FROM Tickets
INNER JOIN Transactions ON Transactions.ObjectId = Tickets.id
INNER JOIN Users old ON old.id = Transactions.OldValue
INNER JOIN Users new ON new.id = Transactions.NewValue
INNER JOIN Users author ON author.id = Transactions.Creator
AND Field = 'Owner'
INTO OUTFILE 'Owner.sql';

--Change Status
SELECT CONCAT('INSERT INTO ticket_change (ticket, time, field, oldvalue, newvalue, author) VALUES (',
Tickets.id,
', EXTRACT(EPOCH FROM ''',
Transactions.Created, '''::timestamp)',
', ''current_status'', ''',
COALESCE(Transactions.OldValue, ''),
''', ''',
COALESCE(Transactions.NewValue, ''),
''', ''',
COALESCE(author.Name, ''),
''');')
FROM Tickets
INNER JOIN Transactions ON Transactions.ObjectId = Tickets.id
INNER JOIN Users author ON author.id = Transactions.Creator
AND Field = 'Status'
INTO OUTFILE 'StatusChange.sql';


--Trac PostGreSQL Database

UPDATE ticket SET Owner = NULL WHERE Owner = 'Nobody';
UPDATE ticket SET Status = 'new' WHERE Owner IS NULL;
UPDATE ticket SET Status = 'assigned' WHERE OWNER IS NOT NULL;
UPDATE ticket SET Status = 'closed'
WHERE
ticket.id IN
(SELECT id FROM ticket INNER JOIN ticket_custom ON ticket_custom.ticket = ticket.id AND ticket_custom.name = 'current_status' AND ticket_custom.value IN ('resolved', 'rejected', 'deleted', 'Released'));
DELETE FROM ticket_change WHERE author = 'RT_System'
ALTER SEQUENCE ticket_id_seq RESTART WITH xxx;
UPDATE ticket SET changetime = (SELECT COALESCE(MAX(time), EXTRACT(EPOCH FROM NOW())) FROM ticket_change WHERE ticket = ticket.id)
UPDATE ticket SET reporter = owner where reporter is null;