0f4df23290
Some checks failed
kotlin / test (push) Has been skipped
python / test (push) Has been skipped
typescript / test (push) Has been skipped
go / build darwin/amd64 (push) Has been cancelled
go / build linux/amd64 (push) Has been cancelled
go / build windows/amd64 (push) Has been cancelled
go / build darwin/arm64 (push) Has been cancelled
go / build linux/arm64 (push) Has been cancelled
go / build windows/arm64 (push) Has been cancelled
go / test (push) Has been cancelled
go / vuln_check (push) Has been cancelled
108 lines
2.5 KiB
SQL
108 lines
2.5 KiB
SQL
-- name: GetAuthor :one
|
|
select id,name , bio from authors
|
|
where id = ? limit 1;
|
|
|
|
-- name: PickyQuery :many
|
|
SELECT id, -- the primary key
|
|
name,
|
|
-- computed downstream
|
|
bio
|
|
FROM authors
|
|
-- soft-deleted rows are filtered
|
|
WHERE bio IS NOT NULL
|
|
AND id > ?;
|
|
|
|
-- name: InlineBlock :many
|
|
SELECT /* inline note */ id, name FROM authors ORDER BY name;
|
|
|
|
-- name: ListAuthors :many
|
|
SELECT id, name, bio FROM authors
|
|
ORDER BY name;
|
|
|
|
-- name: GetEvent :one
|
|
SELECT "EventName", "order"
|
|
FROM "Events" WHERE id = ? LIMIT 1;
|
|
|
|
-- name: CountSigils :one
|
|
SELECT count(*) FROM authors
|
|
WHERE id <> @at_param AND id <> :colon_param AND id <> $dollar_param;
|
|
|
|
-- name: TopAuthors :many
|
|
WITH ranked AS (
|
|
SELECT id, name FROM authors
|
|
)
|
|
SELECT * FROM ranked;
|
|
|
|
-- name: AddAuthor :exec
|
|
INSERT INTO authors (name, bio)
|
|
VALUES (?, ?);
|
|
|
|
-- name: MakeScratch :exec
|
|
CREATE TABLE scratch (
|
|
id INTEGER NOT NULL,
|
|
label TEXT
|
|
);
|
|
|
|
-- name: MakeMeasurements :exec
|
|
CREATE TABLE IF NOT EXISTS measurements (
|
|
id INTEGER PRIMARY KEY,
|
|
label VARYING CHARACTER(120),
|
|
ratio DECIMAL(10,5),
|
|
note
|
|
);
|
|
|
|
-- name: KeepAutoinc :exec
|
|
CREATE TABLE counters (id INTEGER PRIMARY KEY AUTOINCREMENT, hits INTEGER DEFAULT 0);
|
|
|
|
-- name: CastLabel :one
|
|
SELECT CAST(bio AS VARYING CHARACTER(120)) FROM authors LIMIT 1;
|
|
|
|
-- name: MakeSearchIndex :exec
|
|
CREATE VIRTUAL TABLE IF NOT EXISTS notes_fts USING fts5(body, tag UNINDEXED, tokenize = 'porter');
|
|
|
|
-- name: MakeRecipeIndex :exec
|
|
CREATE VIRTUAL TABLE recipes_fts USING fts5(
|
|
name,
|
|
ingredients
|
|
);
|
|
|
|
-- name: LoginName :one
|
|
SELECT COALESCE(bio, '') AS login FROM authors WHERE id = ? LIMIT 1;
|
|
|
|
-- name: SpelledJoins :many
|
|
SELECT a.id
|
|
FROM authors AS a
|
|
INNER JOIN authors AS b ON a.id = b.id
|
|
LEFT OUTER JOIN authors AS c ON a.id = c.id
|
|
WHERE a.id != ? AND b.id == ? AND c.id <> ?;
|
|
|
|
-- name: CommaJoin :one
|
|
SELECT count(*) FROM authors AS a, authors AS b WHERE a.id = b.id;
|
|
|
|
-- name: PlannerHint :many
|
|
SELECT a.id FROM authors AS a CROSS JOIN authors AS b;
|
|
|
|
-- name: NumberedParams :many
|
|
SELECT id FROM authors WHERE name = ?2 AND bio = ?1;
|
|
|
|
-- name: NewestAndOldest :many
|
|
SELECT id FROM authors
|
|
UNION
|
|
SELECT id FROM authors;
|
|
|
|
-- name: QuickUnion :many
|
|
SELECT id FROM authors UNION ALL SELECT id FROM authors;
|
|
|
|
-- name: EnableForeignKeys :exec
|
|
PRAGMA foreign_keys = 1;
|
|
|
|
-- name: UpsertAuthor :exec
|
|
INSERT INTO authors (id, name)
|
|
VALUES (?, ?)
|
|
ON CONFLICT (id) DO UPDATE SET
|
|
name = excluded.name
|
|
WHERE excluded.name <> '';
|
|
|
|
-- name: QuickUpsert :exec
|
|
INSERT INTO authors (id, name) VALUES (?, ?) ON CONFLICT (id) DO UPDATE SET name = excluded.name;
|