git.iko.soy/crush
on cfdca35 help sign in
crush/internal/db/sql/files.sql
62 lines 1.2 kB history raw
1-- name: GetFile :one
2SELECT *
3FROM files
4WHERE id = ? LIMIT 1;
6-- name: GetFileByPathAndSession :one
7SELECT *
8FROM files
9WHERE path = ? AND session_id = ?
10ORDER BY version DESC, created_at DESC
11LIMIT 1;
13-- name: ListFilesBySession :many
14SELECT *
15FROM files
16WHERE session_id = ?
17ORDER BY version ASC, created_at ASC;
19-- name: ListFilesByPath :many
20SELECT *
21FROM files
22WHERE path = ?
23ORDER BY version DESC, created_at DESC;
25-- name: CreateFile :one
26INSERT INTO files (
27 id,
28 session_id,
29 path,
30 content,
31 version,
32 created_at,
33 updated_at
34) VALUES (
35 ?, ?, ?, ?, ?, strftime('%s', 'now'), strftime('%s', 'now')
37RETURNING *;
39-- name: DeleteFile :exec
40DELETE FROM files
41WHERE id = ?;
43-- name: DeleteSessionFiles :exec
44DELETE FROM files
45WHERE session_id = ?;
47-- name: ListLatestSessionFiles :many
48SELECT f.*
49FROM files f
50INNER JOIN (
51 SELECT path, MAX(version) as max_version, MAX(created_at) as max_created_at
52 FROM files
53 GROUP BY path
54) latest ON f.path = latest.path AND f.version = latest.max_version AND f.created_at = latest.max_created_at
55WHERE f.session_id = ?
56ORDER BY f.path;
58-- name: ListNewFiles :many
59SELECT *
60FROM files
61WHERE is_new = 1
62ORDER BY version DESC, created_at DESC;