No Description
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

queries.sql 1.1KB

123456789101112131415161718192021222324252627282930313233343536373839404142
  1. INSERT INTO
  2. users
  3. SELECT
  4. username,
  5. password,
  6. FROM_UNIXTIME(time)
  7. FROM
  8. authreg LEFT JOIN active ON CONCAT(authreg.username, '@', authreg.realm) = active.`collection-owner`;
  9. INSERT INTO
  10. last
  11. SELECT
  12. username,
  13. time AS seconds,
  14. "" AS state
  15. FROM
  16. authreg, logout
  17. WHERE
  18. `collection-owner` = CONCAT(username, "@", realm);
  19. INSERT INTO
  20. rosterusers
  21. SELECT
  22. SUBSTRING_INDEX(`roster-items`.`collection-owner`,"@",1) AS username,
  23. `roster-items`.jid AS jid,
  24. `roster-items`.name AS nick,
  25. IF(`roster-items`.`to` = 1 AND `roster-items`.`from` = 1, 'B', IF(`roster-items`.`to` = 1,'T','F')) AS suscription,
  26. CASE `roster-items`.ask WHEN 0 THEN 'N' WHEN 1 THEN 'O' ELSE 'I' END AS ask,
  27. '' AS askmessage,
  28. 'N' as server,
  29. '' as subscribe,
  30. 'item' as `type`,
  31. CURRENT_TIMESTAMP as created_at
  32. FROM
  33. `roster-items`
  34. ORDER BY jid;
  35. INSERT INTO
  36. rostergroups
  37. SELECT
  38. SUBSTRING_INDEX(`collection-owner`,"@", 1) AS username,
  39. jid,
  40. `group` AS grp
  41. FROM
  42. `roster-groups`;