G2A Many GEOs
SHARE
TWEET

Untitled

a guest Apr 8th, 2020 332 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- Server version: 10.1.44-MariaDB-0+deb9u1
  2.  
  3. SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
  4. SET AUTOCOMMIT = 0;
  5. START TRANSACTION;
  6. SET time_zone = "+00:00";
  7.  
  8. --
  9. -- TABLES
  10. --
  11.  
  12. CREATE TABLE `wpDusers` (
  13.   `ID` bigint(20) UNSIGNED NOT NULL,
  14.   `user_email` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  15. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  16.  
  17. CREATE TABLE `wpDusermeta` (
  18.   `umeta_id` bigint(20) UNSIGNED NOT NULL,
  19.   `user_id` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
  20.   `meta_key` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  21.   `meta_value` longtext COLLATE utf8mb4_unicode_ci
  22. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  23.  
  24. CREATE TABLE `ST_users` (
  25.   `id` int(11) NOT NULL,
  26.   `wpid` int(11) NOT NULL,
  27.   `firstname` varchar(100) DEFAULT NULL,
  28.   `lastname` varchar(100) DEFAULT NULL,
  29.   `fullname` varchar(100) AS (CONCAT(firstname,' ',lastname)) PERSISTENT,
  30.   `email` varchar(100) NOT NULL,
  31.   `wp_role` varchar(100) NOT NULL,
  32.   `role` varchar(100) AS (MID(`wp_role`, LOCATE('"', `wp_role`)+1, LOCATE('"', MID(`wp_role`, LOCATE('"', `wp_role`)+1 , 50))-1)) PERSISTENT,
  33.   `role_id` int(11) DEFAULT '1'
  34. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  35.  
  36. --
  37. -- Indexes for table `ST_users`
  38. --
  39.  
  40. ALTER TABLE `ST_users`
  41. ADD PRIMARY KEY (`wpid`),
  42. ADD KEY `fullname` (`fullname`),
  43. ADD KEY `wpid` (`wpid`);
  44. ALTER TABLE `ST_users`
  45. MODIFY `wpid` int(11) NOT NULL AUTO_INCREMENT;
  46.  
  47. --
  48. -- Indexes for table `wpDusermeta`
  49. --
  50.  
  51. ALTER TABLE `wpDusermeta`
  52. ADD PRIMARY KEY (`umeta_id`),
  53. ADD KEY `user_id` (`user_id`),
  54. ADD KEY `meta_key` (`meta_key`(191));
  55. ALTER TABLE `wpDusermeta`
  56. MODIFY `umeta_id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;
  57.  
  58. --
  59. -- Indexes for table `wpDusers`
  60. --
  61.  
  62. ALTER TABLE `wpDusers`
  63. ADD PRIMARY KEY (`ID`),
  64. ADD KEY `user_email` (`user_email`);
  65. ALTER TABLE `wpDusers`
  66. MODIFY `ID` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;
  67.  
  68. -- TRIGGER
  69.  
  70. DELIMITER $$
  71. CREATE TRIGGER `ST_copy_users_v2` AFTER INSERT ON `wpDusers` FOR EACH ROW BEGIN
  72.  
  73. DECLARE id_i int(11);
  74. DECLARE email_i varchar(100);
  75. DECLARE role_i varchar(100);
  76.  
  77. SELECT NEW.ID, NEW.user_email,
  78. wpDusermeta.meta_value into id_i, email_i, role_i
  79. FROM wpDusers
  80. INNER JOIN wpDusermeta ON wpDusers.ID = wpDusermeta.user_id
  81. WHERE wpDusermeta.meta_key = 'wpDcapabilities'
  82. order by wpDusers.ID desc limit 0 , 1;
  83.  
  84. INSERT INTO ST_users (wpid, email, wp_role) VALUES (id_i, email_i, role_i);
  85.  
  86. END
  87. $$
  88. DELIMITER ;
  89.  
  90. -- INSERT NEW USERS
  91.  
  92. INSERT INTO wpDusers (ID, user_email)
  93. VALUES(1, email@email.mail)
  94. INSERT INTO wpDusermeta (umeta_id, user_id, meta_key, meta_value)
  95. VALUES(1, 1, 'wpDcapabilities', '_{_administrator_}_')
  96.  
  97. INSERT INTO wpDusers (ID, user_email)
  98. VALUES(2, email2@email.mail)
  99. INSERT INTO wpDusermeta (umeta_id, user_id, meta_key, meta_value)
  100. VALUES(2, 2, 'wpDcapabilities', '_{_student_}_')
  101.  
  102. INSERT INTO wpDusers (ID, user_email)
  103. VALUES(3, email2@email.mail)
  104. INSERT INTO wpDusermeta (umeta_id, user_id, meta_key, meta_value)
  105. VALUES(3, 3, 'wpDcapabilities', '_{_pupil_}_')
  106.  
  107. -- Return Wordpress data
  108.  
  109. SELECT wpDusers.ID, wpDusers.user_email, wpDusermeta.meta_value
  110. FROM wpDusers
  111. INNER JOIN wpDusermeta ON wpDusers.ID = wpDusermeta.user_id
  112. WHERE wpDusermeta.meta_key = 'wpDcapabilities'
  113. order by wpDusers.ID desc limit 1;
  114.  
  115. -- Return ST data
  116.  
  117. SELECT * FROM ST_users
RAW Paste Data
Ledger Nano X - The secure hardware wallet
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
Top