web_push.go 3.7 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132
  1. package server
  2. import (
  3. "database/sql"
  4. _ "github.com/mattn/go-sqlite3" // SQLite driver
  5. )
  6. // Messages cache
  7. const (
  8. createWebPushSubscriptionsTableQuery = `
  9. BEGIN;
  10. CREATE TABLE IF NOT EXISTS web_push_subscriptions (
  11. id INTEGER PRIMARY KEY AUTOINCREMENT,
  12. topic TEXT NOT NULL,
  13. username TEXT,
  14. endpoint TEXT NOT NULL,
  15. key_auth TEXT NOT NULL,
  16. key_p256dh TEXT NOT NULL,
  17. updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
  18. );
  19. CREATE INDEX IF NOT EXISTS idx_topic ON web_push_subscriptions (topic);
  20. CREATE INDEX IF NOT EXISTS idx_endpoint ON web_push_subscriptions (endpoint);
  21. CREATE UNIQUE INDEX IF NOT EXISTS idx_topic_endpoint ON web_push_subscriptions (topic, endpoint);
  22. COMMIT;
  23. `
  24. insertWebPushSubscriptionQuery = `
  25. INSERT OR REPLACE INTO web_push_subscriptions (topic, username, endpoint, key_auth, key_p256dh)
  26. VALUES (?, ?, ?, ?, ?);
  27. `
  28. deleteWebPushSubscriptionByEndpointQuery = `DELETE FROM web_push_subscriptions WHERE endpoint = ?`
  29. deleteWebPushSubscriptionByUsernameQuery = `DELETE FROM web_push_subscriptions WHERE username = ?`
  30. deleteWebPushSubscriptionByTopicAndEndpointQuery = `DELETE FROM web_push_subscriptions WHERE topic = ? AND endpoint = ?`
  31. selectWebPushSubscriptionsForTopicQuery = `SELECT endpoint, key_auth, key_p256dh, username FROM web_push_subscriptions WHERE topic = ?`
  32. selectWebPushSubscriptionsCountQuery = `SELECT COUNT(*) FROM web_push_subscriptions`
  33. )
  34. type webPushSubscriptionStore struct {
  35. db *sql.DB
  36. }
  37. func newWebPushSubscriptionStore(filename string) (*webPushSubscriptionStore, error) {
  38. db, err := sql.Open("sqlite3", filename)
  39. if err != nil {
  40. return nil, err
  41. }
  42. if err := setupSubscriptionDb(db); err != nil {
  43. return nil, err
  44. }
  45. webPushSubscriptionStore := &webPushSubscriptionStore{
  46. db: db,
  47. }
  48. return webPushSubscriptionStore, nil
  49. }
  50. func setupSubscriptionDb(db *sql.DB) error {
  51. // If 'messages' table does not exist, this must be a new database
  52. rowsMC, err := db.Query(selectWebPushSubscriptionsCountQuery)
  53. if err != nil {
  54. return setupNewSubscriptionDb(db)
  55. }
  56. rowsMC.Close()
  57. return nil
  58. }
  59. func setupNewSubscriptionDb(db *sql.DB) error {
  60. if _, err := db.Exec(createWebPushSubscriptionsTableQuery); err != nil {
  61. return err
  62. }
  63. return nil
  64. }
  65. func (c *webPushSubscriptionStore) AddSubscription(topic string, username string, subscription webPushSubscribePayload) error {
  66. _, err := c.db.Exec(
  67. insertWebPushSubscriptionQuery,
  68. topic,
  69. username,
  70. subscription.BrowserSubscription.Endpoint,
  71. subscription.BrowserSubscription.Keys.Auth,
  72. subscription.BrowserSubscription.Keys.P256dh,
  73. )
  74. return err
  75. }
  76. func (c *webPushSubscriptionStore) RemoveSubscription(topic string, endpoint string) error {
  77. _, err := c.db.Exec(
  78. deleteWebPushSubscriptionByTopicAndEndpointQuery,
  79. topic,
  80. endpoint,
  81. )
  82. return err
  83. }
  84. func (c *webPushSubscriptionStore) GetSubscriptionsForTopic(topic string) (subscriptions []webPushSubscription, err error) {
  85. rows, err := c.db.Query(selectWebPushSubscriptionsForTopicQuery, topic)
  86. if err != nil {
  87. return nil, err
  88. }
  89. defer rows.Close()
  90. data := []webPushSubscription{}
  91. for rows.Next() {
  92. i := webPushSubscription{}
  93. err = rows.Scan(&i.BrowserSubscription.Endpoint, &i.BrowserSubscription.Keys.Auth, &i.BrowserSubscription.Keys.P256dh, &i.Username)
  94. if err != nil {
  95. return nil, err
  96. }
  97. data = append(data, i)
  98. }
  99. return data, nil
  100. }
  101. func (c *webPushSubscriptionStore) ExpireWebPushEndpoint(endpoint string) error {
  102. _, err := c.db.Exec(
  103. deleteWebPushSubscriptionByEndpointQuery,
  104. endpoint,
  105. )
  106. return err
  107. }
  108. func (c *webPushSubscriptionStore) ExpireWebPushForUser(username string) error {
  109. _, err := c.db.Exec(
  110. deleteWebPushSubscriptionByUsernameQuery,
  111. username,
  112. )
  113. return err
  114. }
  115. func (c *webPushSubscriptionStore) Close() error {
  116. return c.db.Close()
  117. }