1
import { sql } from "drizzle-orm";
2
import {
3
  check,
4
  index,
5
  integer,
6
  primaryKey,
7
  sqliteTable,
8
  text,
9
  uniqueIndex,
10
  type AnySQLiteColumn,
11
} from "drizzle-orm/sqlite-core";
12
 
13
import type { FileChangeType, LockDepth, LockScope, NodeKind, PendingUploadState } from "@/worker/db/types";
14
import type { PendingUploadExpectedState } from "@/worker/db/file-dav-do/types";
15
 
16
export const fileMeta = sqliteTable("meta", {
17
  key: text("key").primaryKey(),
18
  value: text("value").notNull(),
19
});
20
 
21
export const blobs = sqliteTable(
22
  "blobs",
23
  {
24
    blobId: text("blob_id").primaryKey(),
25
    blobKey: text("blob_key").notNull(),
26
    size: integer("size").notNull(),
27
    r2Etag: text("r2_etag"),
28
    sha256: text("sha256"),
29
    refcount: integer("refcount").notNull().default(0),
30
    createdAtMs: integer("created_at_ms").notNull(),
31
  },
32
  (table) => [
33
    uniqueIndex("blobs_key_unique").on(table.blobKey),
34
    index("blobs_refcount_idx").on(table.refcount),
35
    index("blobs_created_idx").on(table.createdAtMs),
36
  ],
37
);
38
 
39
export const nodes = sqliteTable(
40
  "nodes",
41
  {
42
    id: text("id").primaryKey(),
43
    parentId: text("parent_id").references((): AnySQLiteColumn => nodes.id, { onDelete: "cascade" }),
44
    name: text("name").notNull(),
45
    kind: text("kind").notNull().$type<NodeKind>(),
46
    blobId: text("blob_id").references(() => blobs.blobId, { onDelete: "restrict" }),
47
    size: integer("size").notNull().default(0),
48
    contentType: text("content_type"),
49
    contentLanguage: text("content_language"),
50
    etag: text("etag"),
51
    rootMarker: text("root_marker").$type<"root">(),
52
    createdAtMs: integer("created_at_ms").notNull(),
53
    modifiedAtMs: integer("modified_at_ms").notNull(),
54
    version: integer("version").notNull().default(1),
55
  },
56
  (table) => [
57
    uniqueIndex("nodes_parent_name_unique").on(table.parentId, table.name),
58
    uniqueIndex("nodes_root_marker_unique").on(table.rootMarker),
59
    index("nodes_parent_kind_idx").on(table.parentId, table.kind),
60
    index("nodes_blob_idx").on(table.blobId),
61
    index("nodes_modified_idx").on(table.modifiedAtMs),
62
    check("nodes_kind_check", sql`${table.kind} in ('collection', 'file')`),
63
    check("nodes_root_marker_check", sql`${table.rootMarker} is null or ${table.rootMarker} = 'root'`),
64
    check("nodes_collection_blob_check", sql`${table.kind} != 'collection' or ${table.blobId} is null`),
65
  ],
66
);
67
 
68
export const deadProps = sqliteTable(
69
  "dead_props",
70
  {
71
    nodeId: text("node_id")
72
      .notNull()
73
      .references(() => nodes.id, { onDelete: "cascade" }),
74
    nsUri: text("ns_uri").notNull(),
75
    localName: text("local_name").notNull(),
76
    xmlValue: text("xml_value").notNull(),
77
  },
78
  (table) => [primaryKey({ columns: [table.nodeId, table.nsUri, table.localName] })],
79
);
80
 
81
export const locks = sqliteTable(
82
  "locks",
83
  {
84
    token: text("token").primaryKey(),
85
    rootNodeId: text("root_node_id").references(() => nodes.id, { onDelete: "cascade" }),
86
    rootPath: text("root_path").notNull(),
87
    ownerXml: text("owner_xml").notNull(),
88
    principalSubjectId: text("principal_subject_id").notNull(),
89
    scope: text("scope").notNull().$type<LockScope>(),
90
    depth: text("depth").notNull().$type<LockDepth>(),
91
    createdAtMs: integer("created_at_ms").notNull(),
92
    expiresAtMs: integer("expires_at_ms").notNull(),
93
  },
94
  (table) => [
95
    index("locks_root_node_idx").on(table.rootNodeId),
96
    index("locks_root_path_idx").on(table.rootPath),
97
    index("locks_expires_idx").on(table.expiresAtMs),
98
    index("locks_principal_idx").on(table.principalSubjectId),
99
    check("locks_scope_check", sql`${table.scope} in ('exclusive', 'shared')`),
100
    check("locks_depth_check", sql`${table.depth} in ('0', 'infinity')`),
101
  ],
102
);
103
 
104
export const pendingUploads = sqliteTable(
105
  "pending_uploads",
106
  {
107
    uploadId: text("upload_id").primaryKey(),
108
    nodeId: text("node_id").references(() => nodes.id, { onDelete: "set null" }),
109
    path: text("path").notNull(),
110
    blobId: text("blob_id").notNull(),
111
    blobKey: text("blob_key").notNull(),
112
    createdAtMs: integer("created_at_ms").notNull(),
113
    expiresAtMs: integer("expires_at_ms").notNull(),
114
    state: text("state").notNull().$type<PendingUploadState>(),
115
    expectedState: text("expected_state_json", { mode: "json" }).$type<PendingUploadExpectedState>(),
116
  },
117
  (table) => [
118
    index("pending_uploads_state_expires_idx").on(table.state, table.expiresAtMs),
119
    index("pending_uploads_path_idx").on(table.path),
120
    index("pending_uploads_blob_idx").on(table.blobId),
121
    check("pending_uploads_state_check", sql`${table.state} in ('pending', 'committed', 'aborted')`),
122
  ],
123
);
124
 
125
export const changes = sqliteTable(
126
  "changes",
127
  {
128
    seq: integer("seq").primaryKey({ autoIncrement: true }),
129
    nodeId: text("node_id").references(() => nodes.id, { onDelete: "set null" }),
130
    href: text("href").notNull(),
131
    changeType: text("change_type").notNull().$type<FileChangeType>(),
132
    changedAtMs: integer("changed_at_ms").notNull(),
133
  },
134
  (table) => [
135
    index("changes_href_idx").on(table.href),
136
    index("changes_changed_idx").on(table.changedAtMs),
137
    check("changes_type_check", sql`${table.changeType} in ('created', 'updated', 'deleted', 'moved')`),
138
  ],
139
);
140
 
141
export type FileMetaRow = typeof fileMeta.$inferSelect;
142
export type NewFileMetaRow = typeof fileMeta.$inferInsert;
143
export type FileBlobRow = typeof blobs.$inferSelect;
144
export type NewFileBlobRow = typeof blobs.$inferInsert;
145
export type FileNodeRow = typeof nodes.$inferSelect;
146
export type NewFileNodeRow = typeof nodes.$inferInsert;
147
export type FileDeadPropRow = typeof deadProps.$inferSelect;
148
export type NewFileDeadPropRow = typeof deadProps.$inferInsert;
149
export type FileLockRow = typeof locks.$inferSelect;
150
export type NewFileLockRow = typeof locks.$inferInsert;
151
export type PendingUploadRow = typeof pendingUploads.$inferSelect;
152
export type NewPendingUploadRow = typeof pendingUploads.$inferInsert;
153
export type FileChangeRow = typeof changes.$inferSelect;