Guova

SQL MFGG TCSMS

Jul 23rd, 2021
1,341
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE TABLE `tsms_submission_sprites` AS
  2.   SELECT r.uid, r.title, r.description, r.author_override, r.created, r.updated, r.queue_code, r.ghost, r.accept_date, r.update_accept_date, r.decision, r.catwords, z.*
  3.   FROM tsms_res_gfx z
  4.   LEFT JOIN tsms_resources r ON r.eid = z.eid && r.type = 1
  5. ;
  6. ALTER TABLE `tsms_submission_sprites` ADD `id` INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (`id`);
  7. ALTER TABLE `tsms_submission_sprites` DROP `eid`;
  8.  
  9.  
  10.  
  11. CREATE TABLE tsms_submission_games AS
  12.   SELECT r.uid, r.title, r.description, r.author_override, r.created, r.updated, r.queue_code, r.ghost, r.accept_date, r.update_accept_date, r.decision, r.catwords, z.*
  13.   FROM tsms_res_games z
  14.   LEFT JOIN tsms_resources r ON r.eid = z.eid && r.type = 2
  15. ;
  16. ALTER TABLE `tsms_submission_games` ADD `id` INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (`id`);
  17. UPDATE `tsms_submission_games` g SET g.eid = (
  18.   SELECT r.rid
  19.   FROM `tsms_resources` r
  20.   WHERE r.eid = g.eid && r.type = 2
  21. );
  22.  
  23.  
  24.  
  25. CREATE TABLE tsms_submission_hacks AS
  26.   SELECT r.uid, r.title, r.description, r.author_override, r.created, r.updated, r.queue_code, r.ghost, r.accept_date, r.update_accept_date, r.decision, r.catwords, z.*
  27.   FROM tsms_res_hacks z
  28.   LEFT JOIN tsms_resources r ON r.eid = z.eid && r.type = 7
  29. ;
  30. ALTER TABLE `tsms_submission_hacks` ADD `id` INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (`id`);
  31.  
  32.  
  33.  
  34. CREATE TABLE tsms_submission_reviews AS
  35.   SELECT r.uid, r.title, r.description, r.author_override, r.created, r.updated, r.queue_code, r.ghost, r.accept_date, r.update_accept_date, r.decision, r.catwords, z.*
  36.   FROM tsms_res_reviews z
  37.   LEFT JOIN tsms_resources r ON r.eid = z.eid && r.type = 3
  38. ;
  39. ALTER TABLE `tsms_submission_reviews` ADD `id` INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (`id`);
  40. ALTER TABLE `tsms_submission_reviews` ADD `type` INT NOT NULL DEFAULT '0' AFTER `score`;
  41. UPDATE `tsms_submission_reviews` r SET r.gid = (
  42.   SELECT z.id
  43.   FROM tsms_submission_games z
  44.   LEFT JOIN tsms_resources s ON s.eid = z.eid && s.type = 2
  45.   WHERE s.rid = r.gid
  46. ), r.type = 1;
  47. UPDATE `tsms_submission_reviews` r SET r.gid = (
  48.   SELECT g.id FROM `tsms_submission_hacks` h
  49.   RIGHT JOIN `tsms_resources` s ON s.eid = h.eid
  50.   WHERE s.rid = r.gid
  51. ), r.type = 2 WHERE r.type = 0;
RAW Paste Data