A248

Schema which causes Flyway parsing to fail when upgrading 7.0.4->7.5.0

Jan 14th, 2021 (edited)
202
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 4.30 KB | None | 0 0
  1.  
  2. -- Views
  3.  
  4. /*
  5.  
  6. Create views
  7.  
  8. The following 2 queries are executed for each punishment type with the exclusion of KICK
  9. They are both designed to simplify querying active punishments
  10. Replace <lowerNamePlural> is replaced with the relevant punishment type
  11. */
  12.  
  13. /*
  14. CREATE OR REPLACE VIEW `libertybans_simple_<lowerNamePlural>` AS
  15. SELECT `puns`.`id`, `puns`.`type`, `thetype`.`victim`, `thetype`.`victim_type`,
  16. `puns`.`operator`, `puns`.`reason`, `puns`.`scope`, `puns`.`start`, `puns`.`end`
  17. FROM `libertybans_<lowerNamePlural>` `thetype` INNER JOIN `libertybans_punishments` `puns`
  18. ON `puns`.`id` = `thetype`.`id`;
  19.  
  20. CREATE OR REPLACE VIEW `libertybans_applicable_<lowerNamePlural>` AS
  21. SELECT `puns`.`id`, `puns`.`type`, `puns`.`victim`, `puns`.`victim_type`, `puns`.`operator`,
  22. `puns`.`reason`, `puns`.`scope`, `puns`.`start`, `puns`.`end`, `addrs`.`uuid`, `addrs`.`address`
  23. FROM `libertybans_simple_<lowerNamePlural>` `puns` INNER JOIN `libertybans_addresses` `addrs`
  24. ON (`puns`.`victim_type` = 'PLAYER' AND `puns`.`victim` = `addrs`.`uuid`
  25. OR `puns`.`victim_type` = 'ADDRESS' AND `puns`.`victim` = `addrs`.`address`);
  26. */
  27.  
  28. -- Ban
  29.  
  30. CREATE VIEW `libertybans_simple_bans` AS
  31. SELECT `puns`.`id`, `puns`.`type`, `thetype`.`victim`, `thetype`.`victim_type`,
  32. `puns`.`operator`, `puns`.`reason`, `puns`.`scope`, `puns`.`start`, `puns`.`end`
  33. FROM `libertybans_bans` `thetype` INNER JOIN `libertybans_punishments` `puns`
  34. ON `puns`.`id` = `thetype`.`id`;
  35.  
  36. CREATE VIEW `libertybans_applicable_bans` AS
  37. SELECT `puns`.`id`, `puns`.`type`, `puns`.`victim`, `puns`.`victim_type`, `puns`.`operator`,
  38. `puns`.`reason`, `puns`.`scope`, `puns`.`start`, `puns`.`end`, `addrs`.`uuid`, `addrs`.`address`
  39. FROM `libertybans_simple_bans` `puns` INNER JOIN `libertybans_addresses` `addrs`
  40. ON (`puns`.`victim_type` = 'PLAYER' AND `puns`.`victim` = `addrs`.`uuid`
  41. OR `puns`.`victim_type` = 'ADDRESS' AND `puns`.`victim` = `addrs`.`address`);
  42.  
  43. -- Mute
  44.  
  45. CREATE VIEW `libertybans_simple_mutes` AS
  46. SELECT `puns`.`id`, `puns`.`type`, `thetype`.`victim`, `thetype`.`victim_type`,
  47. `puns`.`operator`, `puns`.`reason`, `puns`.`scope`, `puns`.`start`, `puns`.`end`
  48. FROM `libertybans_mutes` `thetype` INNER JOIN `libertybans_punishments` `puns`
  49. ON `puns`.`id` = `thetype`.`id`;
  50.  
  51. CREATE VIEW `libertybans_applicable_mutes` AS
  52. SELECT `puns`.`id`, `puns`.`type`, `puns`.`victim`, `puns`.`victim_type`, `puns`.`operator`,
  53. `puns`.`reason`, `puns`.`scope`, `puns`.`start`, `puns`.`end`, `addrs`.`uuid`, `addrs`.`address`
  54. FROM `libertybans_simple_mutes` `puns` INNER JOIN `libertybans_addresses` `addrs`
  55. ON (`puns`.`victim_type` = 'PLAYER' AND `puns`.`victim` = `addrs`.`uuid`
  56. OR `puns`.`victim_type` = 'ADDRESS' AND `puns`.`victim` = `addrs`.`address`);
  57.  
  58. -- Warn
  59.  
  60. CREATE VIEW `libertybans_simple_warns` AS
  61. SELECT `puns`.`id`, `puns`.`type`, `thetype`.`victim`, `thetype`.`victim_type`,
  62. `puns`.`operator`, `puns`.`reason`, `puns`.`scope`, `puns`.`start`, `puns`.`end`
  63. FROM `libertybans_warns` `thetype` INNER JOIN `libertybans_punishments` `puns`
  64. ON `puns`.`id` = `thetype`.`id`;
  65.  
  66. CREATE VIEW `libertybans_applicable_warns` AS
  67. SELECT `puns`.`id`, `puns`.`type`, `puns`.`victim`, `puns`.`victim_type`, `puns`.`operator`,
  68. `puns`.`reason`, `puns`.`scope`, `puns`.`start`, `puns`.`end`, `addrs`.`uuid`, `addrs`.`address`
  69. FROM `libertybans_simple_warns` `puns` INNER JOIN `libertybans_addresses` `addrs`
  70. ON (`puns`.`victim_type` = 'PLAYER' AND `puns`.`victim` = `addrs`.`uuid`
  71. OR `puns`.`victim_type` = 'ADDRESS' AND `puns`.`victim` = `addrs`.`address`);
  72.  
  73. -- Other helpers
  74.  
  75. CREATE VIEW `libertybans_simple_history` AS
  76. SELECT `puns`.`id`, `puns`.`type`, `thetype`.`victim`, `thetype`.`victim_type`,
  77. `puns`.`operator`, `puns`.`reason`, `puns`.`scope`, `puns`.`start`, `puns`.`end`
  78. FROM `libertybans_history` `thetype` INNER JOIN `libertybans_punishments` `puns`
  79. ON `puns`.`id` = `thetype`.`id`;
  80.  
  81. CREATE VIEW `libertybans_simple_active` AS
  82. SELECT * FROM `libertybans_simple_bans` UNION ALL
  83. SELECT * FROM `libertybans_simple_mutes` UNION ALL
  84. SELECT * FROM `libertybans_simple_warns`;
  85.  
  86. -- Strict account links
  87.  
  88. CREATE VIEW `libertybans_strict_links` AS
  89. SELECT `addrs1`.`uuid` `uuid1`, `addrs2`.`uuid` `uuid2`
  90. FROM `libertybans_addresses` `addrs1` INNER JOIN `libertybans_addresses` `addrs2`
  91. ON `addrs1`.`address` = `addrs2`.`address`;
  92.  
Add Comment
Please, Sign In to add comment