Privacy-first password manager with built-in email aliasing. Fully encrypted and self-hostable.
/**
* SQL query constants for Folder operations.
* Centralizes all folder-related queries to avoid duplication.
*
* A folder is keyed by `(ManifestId, Id)` like every other manifest-scoped row, so each statement below
* takes the folder's manifest as well as its id: two manifests may hold a folder with the same id, and
* matching on the id alone would rename or delete the other manifest's folder along with this one.
*/
export class FolderQueries {
/**
* Get all active folders.
*/
public static readonly GET_ALL = `
SELECT Id, Name, ParentFolderId, Weight, ManifestId
FROM Folders
WHERE IsDeleted = 0
ORDER BY Weight, Name`;
/**
* Whether a live folder exists under this manifest-qualified key.
*/
public static readonly EXISTS = `
SELECT 1 AS Found
FROM Folders
WHERE Id = ? AND ManifestId = ? AND IsDeleted = 0`;
/**
* Insert a new folder into the manifest the caller names, which is its parent folder's own.
*/
public static readonly INSERT = `
INSERT INTO Folders (Id, Name, ParentFolderId, ManifestId, Weight, IsDeleted, CreatedAt, UpdatedAt)
VALUES (?, ?, ?, ?, 0, 0, ?, ?)`;
/**
* Update folder name.
*/
public static readonly UPDATE_NAME = `
UPDATE Folders
SET Name = ?,
UpdatedAt = ?
WHERE Id = ? AND ManifestId = ?`;
/**
* Soft delete folder.
*/
public static readonly SOFT_DELETE = `
UPDATE Folders
SET IsDeleted = 1,
UpdatedAt = ?
WHERE Id = ? AND ManifestId = ?`;
/**
* Clear folder reference from items (set to NULL).
*/
public static readonly CLEAR_ITEMS_FOLDER = `
UPDATE Items
SET FolderId = NULL,
UpdatedAt = ?
WHERE FolderId = ? AND ManifestId = ?`;
/**
* Move items to a different folder of the same manifest.
*/
public static readonly MOVE_ITEMS_TO_FOLDER = `
UPDATE Items
SET FolderId = ?,
UpdatedAt = ?
WHERE FolderId = ? AND ManifestId = ?`;
/**
* Trash items in folder.
*/
public static readonly TRASH_ITEMS_IN_FOLDER = `
UPDATE Items
SET DeletedAt = ?,
UpdatedAt = ?,
FolderId = NULL
WHERE FolderId = ? AND ManifestId = ? AND IsDeleted = 0 AND DeletedAt IS NULL`;
/**
* Get all child folder IDs (direct children only), within the parent's own manifest.
*/
public static readonly GET_CHILD_FOLDER_IDS = `
SELECT Id
FROM Folders
WHERE ParentFolderId = ? AND ManifestId = ? AND IsDeleted = 0`;
/**
* The direct child folders of a folder, with their names, within the parent's own manifest.
*/
public static readonly GET_CHILD_FOLDERS = `
SELECT Id, Name
FROM Folders
WHERE ParentFolderId = ? AND ManifestId = ? AND IsDeleted = 0`;
/**
* The top-level folders of a manifest, with their names.
*/
public static readonly GET_TOP_LEVEL_FOLDERS = `
SELECT Id, Name
FROM Folders
WHERE ParentFolderId IS NULL AND ManifestId = ? AND IsDeleted = 0`;
/**
* The live items at the top level of a manifest, outside any folder.
*/
public static readonly GET_TOP_LEVEL_ITEM_IDS = `
SELECT Id
FROM Items
WHERE FolderId IS NULL AND ManifestId = ? AND IsDeleted = 0`;
/**
* The live items directly inside a folder.
*/
public static readonly GET_ITEM_IDS_IN_FOLDER = `
SELECT Id
FROM Items
WHERE FolderId = ? AND ManifestId = ? AND IsDeleted = 0`;
/**
* Move one item into a folder of another manifest; a trigger carries its child rows along and leaves a tombstone.
* Params: folder id (or NULL), target manifest id, timestamp, item id, source manifest id.
*/
public static readonly MOVE_ITEM_TO_MANIFEST = `
UPDATE Items
SET FolderId = ?,
ManifestId = ?,
UpdatedAt = ?
WHERE Id = ? AND ManifestId = ?`;
/**
* Update parent folder for child folders.
*/
public static readonly UPDATE_PARENT_FOLDER = `
UPDATE Folders
SET ParentFolderId = ?,
UpdatedAt = ?
WHERE ParentFolderId = ? AND ManifestId = ?`;
/**
* The shared manifests this vault holds, with their names. This is locally created bookkeeping and is not synced to the server.
* Params: the personal manifest id.
*/
public static readonly GET_SHARED_MANIFESTS = `
SELECT Id AS ManifestId, Name
FROM Manifests
WHERE Id <> ? COLLATE NOCASE
ORDER BY Name`;
}