SHARE
TWEET

Untitled

a guest Jul 22nd, 2019 58 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE TABLE `RaidPlayers` (
  2.   `raidplayer_id` INT NOT NULL AUTO_INCREMENT,
  3.   `username` VARCHAR(17),
  4.   `username_lowwercase` VARCHAR(17),
  5.   `uniquie_id` VARCHAR(32),
  6.   `personal_warp_id` INT,
  7.   `coins` INT,
  8.   `join_timestamp` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  9.   PRIMARY KEY (`raidplayer_id`)
  10. );
  11.  
  12. CREATE TABLE `RaidTeams` (
  13.   `team_id` INT NOT NULL AUTO_INCREMENT,
  14.   `name` VARCHAR(32),
  15.   `tag` VARCHAR(10),
  16.   `rally_warp_id` INT,
  17.   `hq_warp_id` INT,
  18.   `max_members` INT,
  19.   `donator_rank` VARCHAR(17),
  20.   PRIMARY KEY (`team_id`),
  21.   KEY `FK` (`rally_warp_id`, `hq_warp_id`)
  22. );
  23.  
  24. CREATE TABLE `TeamMembers` (
  25.   `raidplayer_id` INT ,
  26.   `team_id` INT,
  27.   `team_role` VARCHAR(17),
  28.   `is_active` TINYINT,
  29.   `join_timestamp` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  30.   PRIMARY KEY (`raidplayer_id`, `team_id`)
  31. );
  32.  
  33. CREATE TABLE `Warps` (
  34.   `warp_id` INT NOT NULL AUTO_INCREMENT,
  35.   `world_name` VARCHAR(50),
  36.   `x` INT,
  37.   `y` INT,
  38.   `z` INT,
  39.   `creation_timestamp` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  40.   PRIMARY KEY (`warp_id`)
  41. );
  42.  
  43. INSERT INTO Warps (world_name, x, y, z) VALUES ('world', 1, 2, 3);
  44. INSERT INTO Warps (world_name, x, y, z) VALUES ('world', 4, 5, 6);
  45. INSERT INTO Warps (world_name, x, y, z) VALUES ('world', 7, 8, 9);
  46. INSERT INTO RaidPlayers (username, username_lowwercase, uniquie_id, personal_warp_id, coins) VALUES ('Sprock', 'sprock', '5d793eed51b747659b384fdaa00034d7', 1, 100);
  47. INSERT INTO RaidPlayers (username, username_lowwercase, uniquie_id, personal_warp_id, coins) VALUES ('Issy', 'issy', '46a492d220754195b9c3a51363cca82e', 2, 200);
  48. INSERT INTO RaidTeams (name, tag, rally_warp_id, hq_warp_id, max_members, donator_rank) VALUES ('Rebellion', 'Rebel', 1, 3, 10, 'ADMIN');
  49.  
  50. INSERT INTO `TeamMembers` (raidplayer_id, team_id, team_role, is_active) VALUES (1, 1, 'LEADER', 1);
  51. INSERT INTO `TeamMembers` (raidplayer_id, team_id, team_role, is_active) VALUES (2, 1, 'MOD', 1);
  52.  
  53.  
  54. UPDATE `RaidPlayers` SET `coins` = 1 WHERE raidplayer_id = 2;
  55. UPDATE `RaidTeams` SET `rally_warp_id` = 2 WHERE team_id = 1;
  56.  
  57. -- add a new warp and set it as team's warp
  58. INSERT INTO Warps (world_name, x, y, z) VALUES ('world', 10, 11, 12);
  59. UPDATE RaidTeams SET RaidTeams.rally_warp_id = (SELECT LAST_INSERT_ID())
  60.  
  61.  
  62.  
  63.  
  64. ==== QUERIES
  65.  
  66. SELECT * FROM RaidPlayers
  67. INNER JOIN Warps ON `RaidPlayers`.personal_warp_id = `Warps`.warp_id;
  68.  
  69. SELECT * FROM TeamMembers
  70. INNER JOIN RaidTeams ON `RaidTeams`.team_id = `TeamMembers`.team_id
  71. INNER JOIN RaidPlayers ON RaidPlayers.raidplayer_id = TeamMembers.raidplayer_id;
  72.  
  73. SELECT team_id,  name, tag, max_members, donator_rank, rally.world_name as rally_world, rally.x as rally_x, rally.y as rally_y, rally.z as rally_z, hq.world_name as hq_world, hq.x as hq_x, hq.y as hq_y, hq.z as hq_z FROM RaidTeams
  74. INNER JOIN Warps as rally ON rally_warp_id = rally.warp_id
  75. INNER JOIN Warps as hq ON hq_warp_id = hq.warp_id;
  76.  
  77. SELECT * FROM RaidPlayers where `raidplayer_id` = 2;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top