1
/*
2
 *  This file is part of CoCalc: Copyright © 2020 Sagemath, Inc.
3
 *  License: MS-RSL – see LICENSE.md for details
4
 */
5

6
import { Table } from "./types";
7
import { minutes_ago } from "../misc";
8
import { SCHEMA as schema } from "./index";
9

10
/* TODO: for postgres rewrite after done we MIGHT completely redo file_use to eliminate
11
the id field, use project_id, path as a compound primary key, and maybe put users in
12
another table with a relation.  There is also expert discussion about this table in the
13
Hacker News discussion of my PostgreSQL vs ... blog post.
14
*/
15

16
Table({
17
  name: "file_use",
18
  fields: {
19
    id: {
20
      type: "string",
21
      pg_type: "CHAR(40)",
22
    },
23
    project_id: {
24
      type: "uuid",
25
    },
26
    path: {
27
      type: "string",
28
    },
29
    users: {
30
      type: "map",
31
      desc: "{account_id1: {action1: timestamp1, action2:timestamp2}, account_id2: {...}}",
32
      date: "all",
33
    },
34
    last_edited: {
35
      type: "timestamp",
36
    },
37
  },
38
  rules: {
39
    primary_key: "id",
40
    durability: "soft", // loss of some log data not serious, since used only for showing notifications
41
    unique_writes: true, // there is no reason for a user to write the same record twice
42
    db_standby: "safer", // allow doing the initial read part of the query from a standby node.
43
    pg_indexes: ["project_id", "last_edited"],
44

45
    // CRITICAL!  At scale, this query
46
    //    SELECT * FROM file_use WHERE project_id = any(select project_id from projects where users ? '25e2cae4-05c7-4c28-ae22-1e6d3d2e8bb3') ORDER BY last_edited DESC limit 100;
47
    // will take forever due to the query planner being off with its estimation (its the case where there is no such user or no data) and also uses several workers to do an index scan
48
    // We disable the indes scan for this query, which gets rid of the extra workers and runs fine.
49
    pg_indexscan: false,
50

51
    // I put a time limit in pg_where below of to just give genuinely recent notifications,
52
    // and massively reduce server load.  The obvious todo list is to make another file_use
53
    // virtual table that lets you get older entries.
54
    user_query: {
55
      get: {
56
        pg_where: ["last_edited >= NOW() - interval '21 days'", "projects"],
57
        pg_where_load: [
58
          "last_edited >= NOW() - interval '10 days'",
59
          "projects",
60
        ],
61
        pg_changefeed: "projects",
62
        options: [{ order_by: "-last_edited" }, { limit: 200 }], // limit is arbitrary
63
        options_load: [{ order_by: "-last_edited" }, { limit: 70 }], // limit is arbitrary
64
        throttle_changes: 2000,
65
        fields: {
66
          id: null,
67
          project_id: null,
68
          path: null,
69
          users: null,
70
          last_edited: null,
71
        },
72
      },
73
      set: {
74
        fields: {
75
          id(obj, db) {
76
            return db.sha1(obj.project_id, obj.path);
77
          },
78
          project_id: "project_write",
79
          path: true,
80
          users: true,
81
          last_edited: true,
82
        },
83
        required_fields: {
84
          id: true,
85
          project_id: true,
86
          path: true,
87
        },
88
        check_hook(db, obj, account_id, _project_id, cb) {
89
          // hook to note that project is being used (CRITICAL: do not pass path
90
          // into db.touch since that would cause another write to the file_use table!)
91
          // CRITICAL: Only do this if what edit or chat for this user is very recent.
92
          // Otherwise we touch the project just for seeing notifications or opening
93
          // the file, which is confusing and wastes a lot of resources.
94
          const x = obj.users != null ? obj.users[account_id] : undefined;
95
          // edit/chat/open fields are now strings due to using conat and not auto
96
          // autoconverting them to Dates at this point, hence comparing iso strings:
97
          const recent = minutes_ago(3).toISOString();
98
          if (
99
            x != null &&
100
            (x.edit >= recent || x.chat >= recent || x.open >= recent)
101
          ) {
102
            db.touch({ project_id: obj.project_id, account_id });
103
            // Also log that this particular file is being used/accessed; this
104
            // is mainly only for longterm analytics but also the file_use_times
105
            // virtual table queries this.  Note that log_file_access
106
            // is throttled.
107
            db.log_file_access({
108
              project_id: obj.project_id,
109
              account_id,
110
              filename: obj.path,
111
            });
112
          }
113
          cb();
114
        },
115
      },
116
    },
117
  },
118
});
119

120
Table({
121
  name: "crm_file_use",
122
  rules: {
123
    virtual: "file_use",
124
    primary_key: "id",
125
    user_query: {
126
      get: {
127
        admin: true, // only admins can do get queries on this table
128
        fields: schema.file_use.user_query?.get?.fields ?? {},
129
      },
130
    },
131
  },
132
  fields: schema.file_use.fields,
133
});
134

135