1
/*
2
Database
3
*/
4

5
import Database from "better-sqlite3";
6
import { context } from "./config";
7
import {
8
  primaryKey,
9
  type PrimaryKey,
10
  type Filesystem,
11
  type RawFilesystem,
12
  type SetFilesystem,
13
  OWNER_ID_FIELDS,
14
} from "./types";
15
import { is_array, is_date } from "@cocalc/util/misc";
16

17
let db: { [file: string]: Database.Database } = {};
18

19
const tableName = "filesystems";
20
const schema = {
21
  // this uniquely defines the filesystem (it's the compound primary key)
22
  owner_type: "TEXT",
23
  owner_id: "TEXT",
24
  namespace: "TEXT",
25
  name: "TEXT",
26

27
  // data about the filesystem
28
  pool: "TEXT",
29
  archived: "INTEGER",
30
  affinity: "TEXT",
31
  nfs: "TEXT",
32
  snapshots: "TEXT",
33
  last_edited: "TEXT",
34
  last_send_snapshot: "TEXT",
35
  last_bup_backup: "TEXT",
36
  error: "TEXT",
37
  last_error: "TEXT",
38
  used_by_dataset: "INTEGER",
39
  used_by_snapshots: "INTEGER",
40
  quota: "INTEGER",
41
};
42

43
const WHERE_PRIMARY_KEY =
44
  "WHERE namespace=? AND owner_type=? AND owner_id=? AND name=?";
45
function primaryKeyArgs(fs: PrimaryKey) {
46
  const { namespace, owner_type, owner_id, name } = primaryKey(fs);
47
  return [namespace, owner_type, owner_id, name];
48
}
49

50
export function getDb(databaseFile?): Database.Database {
51
  const file = databaseFile ?? context.SQLITE3_DATABASE_FILE;
52
  if (db[file] == null) {
53
    db[file] = new Database(file);
54
    initDb(db[file]);
55
  }
56
  return db[file]!;
57
}
58

59
function initDb(db) {
60
  const columnDefinitions = Object.entries(schema)
61
    .map(([name, type]) => `${name} ${type}`)
62
    .join(", ");
63

64
  // Create table if it doesn't exist
65
  db.prepare(
66
    `CREATE TABLE IF NOT EXISTS ${tableName} (
67
      ${columnDefinitions},
68
      PRIMARY KEY (namespace, owner_type, owner_id, name)
69
    )`,
70
  ).run();
71

72
  // Check for missing columns and add them
73
  const existingColumnsStmt = db.prepare(`PRAGMA table_info(${tableName})`);
74
  const existingColumns = existingColumnsStmt.all().map((row) => row.name);
75

76
  for (const [name, type] of Object.entries(schema)) {
77
    if (!existingColumns.includes(name)) {
78
      db.prepare(`ALTER TABLE ${tableName} ADD COLUMN ${name} ${type}`).run();
79
    }
80
  }
81
}
82

83
// This is extremely dangerous and mainly used for unit testing:
84
export function resetDb() {
85
  const db = new Database(context.SQLITE3_DATABASE_FILE);
86
  db.prepare("DROP TABLE IF EXISTS filesystems").run();
87
  initDb(db);
88
}
89

90
function convertToSqliteType({ value, getFilesystem }) {
91
  if (is_array(value)) {
92
    return value.join(",");
93
  } else if (is_date(value)) {
94
    return value.toISOString();
95
  } else if (typeof value == "boolean") {
96
    return value ? 1 : 0;
97
  } else if (typeof value == "function") {
98
    const x = value(getFilesystem());
99
    if (typeof x == "function") {
100
      throw Error("function must not return a function");
101
    }
102
    // returned value needs to be converted
103
    return convertToSqliteType({ value: x, getFilesystem });
104
  }
105
  return value;
106
}
107

108
export function set(obj: SetFilesystem) {
109
  const pk = primaryKey(obj);
110
  const fields: string[] = [];
111
  const values: any[] = [];
112
  let filesystem: null | Filesystem = null;
113
  const getFilesystem = () => {
114
    if (filesystem == null) {
115
      filesystem = get(pk);
116
    }
117
    return filesystem;
118
  };
119
  for (const field in obj) {
120
    if (pk[field] !== undefined || OWNER_ID_FIELDS.includes(field)) {
121
      continue;
122
    }
123
    fields.push(field);
124
    values.push(convertToSqliteType({ value: obj[field], getFilesystem }));
125
  }
126
  let query = `UPDATE filesystems SET
127
    ${fields.map((field) => `${field}=?`).join(", ")}
128
    ${WHERE_PRIMARY_KEY}
129
  `;
130
  for (const x of primaryKeyArgs(pk)) {
131
    values.push(x);
132
  }
133
  const db = getDb();
134
  db.prepare(query).run(...values);
135
}
136

137
// Call this if something that should never happen, does in fact, happen.
138
// It will set the error state of the filesystem and throw the exception.
139
// Admins will be regularly notified of all filesystems in an error state.
140
export function fatalError(
141
  obj: PrimaryKey & {
142
    err: Error;
143
    desc?: string;
144
  },
145
) {
146
  set({
147
    ...primaryKey(obj),
148
    error: `${obj.err}${obj.desc ? " - " + obj.desc : ""}`,
149
    last_error: new Date(),
150
  });
151
  throw obj.err;
152
}
153

154
export function clearError(fs: PrimaryKey) {
155
  set({ ...fs, error: null });
156
}
157

158
export function clearAllErrors() {
159
  const db = getDb();
160
  db.prepare("UPDATE filesystems SET error=null").run();
161
}
162

163
export function getErrors() {
164
  const db = getDb();
165
  return db
166
    .prepare("SELECT * FROM filesystems WHERE error!=''")
167
    .all() as RawFilesystem[];
168
}
169

170
export function touch(fs: PrimaryKey) {
171
  set({ ...fs, last_edited: new Date() });
172
}
173

174
export function filesystemExists(
175
  fs: PrimaryKey,
176
  databaseFile?: string,
177
): boolean {
178
  const db = getDb(databaseFile);
179
  const x = db
180
    .prepare("SELECT COUNT(*) AS count FROM filesystems " + WHERE_PRIMARY_KEY)
181
    .get(...primaryKeyArgs(fs));
182
  return (x as any).count > 0;
183
}
184

185
export function get(fs: PrimaryKey, databaseFile?: string): Filesystem {
186
  const db = getDb(databaseFile);
187
  const filesystem = db
188
    .prepare("SELECT * FROM filesystems " + WHERE_PRIMARY_KEY)
189
    .get(...primaryKeyArgs(fs)) as any;
190
  if (filesystem == null) {
191
    throw Error(`no filesystem ${JSON.stringify(fs)}`);
192
  }
193
  for (const key of ["nfs", "snapshots"]) {
194
    filesystem[key] = sqliteStringToArray(filesystem[key]);
195
  }
196
  filesystem["archived"] = !!filesystem["archived"];
197
  if (filesystem.last_edited) {
198
    filesystem.last_edited = new Date(filesystem.last_edited);
199
  }
200
  if (filesystem.last_error) {
201
    filesystem.last_error = new Date(filesystem.last_error);
202
  }
203
  return filesystem as Filesystem;
204
}
205

206
export function create(
207
  obj: PrimaryKey & {
208
    pool: string;
209
    affinity?: string;
210
  },
211
) {
212
  if (!obj.pool.startsWith(context.PREFIX)) {
213
    throw Error(`pool must start with ${context.PREFIX} - ${obj.pool}`);
214
  }
215
  getDb()
216
    .prepare(
217
      "INSERT INTO filesystems(namespace, owner_type, owner_id, name, pool, affinity, last_edited) VALUES(?,?,?,?,?,?,?)",
218
    )
219
    .run(
220
      ...primaryKeyArgs(obj),
221
      obj.pool,
222
      obj.affinity,
223
      new Date().toISOString(),
224
    );
225
}
226

227
export function deleteFromDb(fs: PrimaryKey) {
228
  getDb()
229
    .prepare("DELETE FROM filesystems " + WHERE_PRIMARY_KEY)
230
    .run(...primaryKeyArgs(fs));
231
}
232

233
export function getAll({
234
  namespace = context.namespace,
235
}: { namespace?: string } = {}): RawFilesystem[] {
236
  const db = getDb();
237
  return db
238
    .prepare("SELECT * FROM filesystems WHERE namespace=?")
239
    .all(namespace) as RawFilesystem[];
240
}
241

242
export function getNamespacesAndPools(): { namespace: string; pool: string }[] {
243
  const db = getDb();
244
  return db
245
    .prepare("SELECT DISTINCT namespace, pool FROM filesystems")
246
    .all() as any;
247
}
248

249
export function getRecent({
250
  namespace,
251
  cutoff,
252
  databaseFile,
253
}: {
254
  namespace?: string;
255
  cutoff?: Date;
256
  databaseFile?: string;
257
} = {}): RawFilesystem[] {
258
  const db = getDb(databaseFile);
259
  if (cutoff == null) {
260
    cutoff = new Date(Date.now() - 1000 * 60 * 60 * 24 * 7);
261
  }
262
  const query = "SELECT * FROM filesystems WHERE last_edited>=?";
263
  if (namespace == null) {
264
    return db.prepare(query).all(cutoff.toISOString()) as RawFilesystem[];
265
  } else {
266
    return db
267
      .prepare(`${query} AND namespace=?`)
268
      .all(cutoff.toISOString(), namespace) as RawFilesystem[];
269
  }
270
}
271

272
function sqliteStringToArray(s?: string): string[] {
273
  if (!s) {
274
    return [];
275
  }
276
  return s.split(",");
277
}
278

279