vnext: `postgres` -> `sequelize`

* implement `active_month` field for Mastodon `/api/v2/instance` endpoint

6 files changed, 59 insertions, 22 deletions

diff --git a/vnext/server/db/Mastodon.spec.js b/vnext/server/db/Mastodon.spec.js
new file mode 100644
index 00000000..f2edb40b
--- /dev/null
+++ b/vnext/server/db/Mastodon.spec.js
@@ -0,0 +1,21 @@
+import request from 'supertest'
+import express from 'express'
+import { instance } from '../middleware/mastodon'
+import db from './index'
+
+const app = express()
+app.get('/instance', instance)
+
+describe('Mastodon API middleware', () => {
+    it('Inactive users should not be included in Instance response', async () => {
+        await db.query('INSERT INTO users(nick,passw,last_seen) \
+            VALUES(\'ugnich\', \'12345\', NULL), \
+            (\'freefd\', \'12345\', DATETIME(\'now\'))')
+        return request(app)
+        .get('/instance')
+        .expect(200)
+        .then(response => {
+            expect(response.body.usage.users.active_month).toStrictEqual(1)
+        })
+    })
+})
diff --git a/vnext/server/db/Users.js b/vnext/server/db/Users.js
new file mode 100644
index 00000000..3db3eb5b
--- /dev/null
+++ b/vnext/server/db/Users.js
@@ -0,0 +1,27 @@
+import { DataTypes, Op } from 'sequelize'
+import db from './index'
+
+export const User = db.define('user', {
+  id: {
+    type: DataTypes.INTEGER,
+    primaryKey: true
+  },
+  nick: DataTypes.STRING,
+  banned: DataTypes.INTEGER,
+  last_seen: DataTypes.DATE
+})
+
+export const getMonthlyActiveUsers = async () => {
+  const seenDate = new Date()
+  seenDate.setMonth(seenDate.getMonth() - 1)
+  return await User.count({
+    where: {
+      banned: {
+        [Op.eq]: 0
+      },
+      last_seen: {
+        [Op.gt]: seenDate
+      }
+    }
+  })
+}
diff --git a/vnext/server/db/index.js b/vnext/server/db/index.js
index 7a029627..c6c9ca24 100644
--- a/vnext/server/db/index.js
+++ b/vnext/server/db/index.js
@@ -1,5 +1,5 @@
-import postgres from 'postgres'
+import { Sequelize } from 'sequelize'
 
-const sql = postgres({ database: 'juick', host: process.env.PG_HOST })
+const db = new Sequelize(process.env.DATABASE_URL)
 
-export default sql
+export default db
diff --git a/vnext/server/db/users.js b/vnext/server/db/users.js
deleted file mode 100644
index 2201b1ef..00000000
--- a/vnext/server/db/users.js
+++ /dev/null
@@ -1,14 +0,0 @@
-import sql from './index'
-
-/**
- *  Count users
- */
-export async function get_count() {
-    const count = await sql`
-      select
-        count(*)
-      from users
-      where banned=0
-    `
-    return count
-}
diff --git a/vnext/server/index.js b/vnext/server/index.js
index 1fd46557..f27f0908 100644
--- a/vnext/server/index.js
+++ b/vnext/server/index.js
@@ -15,7 +15,6 @@ import { instance } from './middleware/mastodon'
 const PORT = process.env.LISTEN_PORT || 8081
 import path from 'path'
 import { webhook, webhookPath } from './durov'
-import { get_count } from './db/users'
 
 // initialize the application and create the routes
 const app = express()
@@ -50,10 +49,6 @@ router.use(express.static(
 
 app.use(router)
 
-get_count().then(count => {
-    log(`Registered users: ${JSON.stringify(count)}`)
-}).catch(log)
-
 // start the app
 app.listen(PORT, (error) => {
     if (error) {
diff --git a/vnext/server/middleware/mastodon.js b/vnext/server/middleware/mastodon.js
index 3bbad3c4..e29fd736 100644
--- a/vnext/server/middleware/mastodon.js
+++ b/vnext/server/middleware/mastodon.js
@@ -1,8 +1,11 @@
+import { getMonthlyActiveUsers } from '../db/Users'
+
 /**
  * Return content for embedding
  * @type {import('express').RequestParamHandler}
  */
 export const instance = async (req, res) => {
+    const activeUsers = await getMonthlyActiveUsers()
     res.json({
         'domain': 'juick.com',
         'title': 'Microblogging service',
@@ -10,6 +13,11 @@ export const instance = async (req, res) => {
         'version': '2.x',
         'contact': {
             'email': 'support@juick.com'
+        },
+        'usage': {
+            'users': {
+                'active_month': activeUsers
+            }    
         }
     })
 }