A WhatsApp (Web) transport for XMPP.
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.

86 lines
2.3KB

  1. CREATE TABLE configuration (
  2. rev INT PRIMARY KEY,
  3. server VARCHAR NOT NULL,
  4. port INT NOT NULL,
  5. component_name VARCHAR NOT NULL,
  6. shared_secret VARCHAR NOT NULL,
  7. upload_component_name VARCHAR NOT NULL,
  8. allow_archiving BOOL NOT NULL DEFAULT false,
  9. allow_history_fetches BOOL NOT NULL DEFAULT false
  10. );
  11. CREATE TABLE users (
  12. id INTEGER PRIMARY KEY,
  13. jid VARCHAR UNIQUE NOT NULL,
  14. session_data VARCHAR,
  15. enable_archiving BOOL NOT NULL DEFAULT false
  16. );
  17. CREATE TABLE user_contacts (
  18. id INTEGER PRIMARY KEY,
  19. user_id INT NOT NULL REFERENCES users,
  20. wa_jid VARCHAR NOT NULL,
  21. subscription_state VARCHAR NOT NULL DEFAULT 'none',
  22. avatar_url VARCHAR,
  23. name VARCHAR,
  24. notify VARCHAR,
  25. status VARCHAR,
  26. UNIQUE(user_id, wa_jid)
  27. );
  28. CREATE TABLE user_messages (
  29. id INTEGER PRIMARY KEY,
  30. user_id INT NOT NULL REFERENCES users,
  31. xmpp_id VARCHAR NOT NULL,
  32. wa_id VARCHAR NOT NULL,
  33. UNIQUE(user_id, wa_id)
  34. );
  35. CREATE TABLE avatar_data (
  36. avatar_url VARCHAR NOT NULL PRIMARY KEY,
  37. sha1 VARCHAR NOT NULL,
  38. image BLOB NOT NULL
  39. );
  40. CREATE TABLE user_chats (
  41. id INTEGER PRIMARY KEY,
  42. user_id INT NOT NULL REFERENCES users,
  43. wa_jid VARCHAR NOT NULL,
  44. user_resource VARCHAR,
  45. invitation_state VARCHAR NOT NULL DEFAULT 'none',
  46. subject VARCHAR
  47. );
  48. CREATE TABLE user_chat_members (
  49. id INTEGER PRIMARY KEY,
  50. chat_id INT NOT NULL REFERENCES user_chats,
  51. wa_jid VARCHAR NOT NULL,
  52. resource VARCHAR NOT NULL,
  53. affiliation VARCHAR NOT NULL DEFAULT 'member'
  54. );
  55. CREATE TABLE user_chat_joined (
  56. id INTEGER PRIMARY KEY,
  57. chat_id INT NOT NULL REFERENCES user_chats,
  58. jid VARCHAR NOT NULL
  59. );
  60. CREATE TABLE user_chat_history (
  61. id INTEGER PRIMARY KEY,
  62. user_id INT NOT NULL REFERENCES users,
  63. chat_id INT NOT NULL REFERENCES user_chats,
  64. user_from VARCHAR NOT NULL,
  65. ts_unix INT NOT NULL,
  66. xmpp_id VARCHAR NOT NULL,
  67. orig_id VARCHAR,
  68. body VARCHAR NOT NULL,
  69. oob_url VARCHAR
  70. );
  71. CREATE UNIQUE INDEX user_chat_history_unique ON user_chat_history (user_id, chat_id, xmpp_id);
  72. CREATE TABLE administrators (
  73. id INTEGER PRIMARY KEY,
  74. jid VARCHAR UNIQUE NOT NULL
  75. );