raqb/experiments/forum-queries.js

/* eslint-disable no-undef */
"use strict";

// FIXME: Think about whether to do type(clauses) or [ type(), ... clauses ]
// FIXME: Defaults via defaultTo! And make sure to document that these can *only* consist of query objects (literals or expressions), not arbitrary JS
// FIXME: Think about field validation API; both schema-time CHECK constraints and runtime validation
// FIXME: Think about some way to integrate partial cacheing of queries (eg. for determining permissions for roles)
// FIXME: Document that due to how schema creation works, only belongsTo(...) is possible and has(...) isn't
// FIXME: last() in addition to first()? Would need to find a way to combine that with sorting, and think about how to handle that when no sorting criterium is given.
// FIXME: duration() and time parsing
// FIXME: ensure that relations work on collapsed queries, but *only* if the FK column appears in the permitted output columns
// FIXME: Separate batch-insert API so that data(...) calls are composable without implying multiple items
// FIXME: Have a short-hand (non-composable) API for create("foo", {obj}) and select("foo", {whereObj}) type usage? Especially for eg. `select("posts", { id: postID })`
// FIXME: Also for createTable? So that you can do eg. createTable("posts", { title: string() }) instead of the `columns` indirection, when you don't have stuff like composite indexes
// FIXME: Allow passing a custom type to autoID?

let timestamps = {
	created_at: [ timestamp(), defaultTo(now()) ],
	updated_at: [ timestamp(), optional() ]
};

createTable("permissions", {
	role: belongsTo("roles.id"),
	permission: string() // defined in the application code
});

createTable("roles", {
	name: string(),
	color: string()
});

createTable("users", {
	username: string(),
	password_hash: string(),
	email_address: string(),
	is_banned: [ boolean(), defaultTo(false) ],
	ban_reason: [ string(), optional() ],
	last_activity: [ timestamp(), defaultTo(now()), index() ],
	activation_key: [ uuid(), optional(), index() ],
	activation_expiry: [ timestamp(), optional() ],
	password_reset_key: [ uuid(), optional(), index() ],
	password_reset_expiry: [ timestamp(), optional() ]
});

createTable("categories", {
	name: string(),
	created_by: belongsTo("users.id"),
	visible_on_frontpage: [ boolean(), defaultTo(true) ]
});

createTable("threads", {
	title: string(),
	category: belongsTo("categories.id"),
	user: belongsTo("users.id"), // FIXME: Figure out how to auto-detect the column type for relations
	visible: [ boolean(), defaultTo(true) ],
	... timestamps
});

createTable("posts", {
	thread: belongsTo("threads.id"),
	user: belongsTo("users.id"),
	body: string(),
	visible: [ boolean(), defaultTo(true) ],
	... timestamps
});

////////////////////////////////////////////////////////////

// List active users and include their role information for highlighting moderators etc.

function timeAgo(time) {
	return subtract(now(), duration(time));
}

select("users", [
	where({ last_activity: lessThan(timeAgo("1h")) }),
	withRelations({ role: belongsTo("role") })
]);

// Count the active users by role
// NOTE: This returns an object { role, count } where `role` is the actual data from the `roles` table

select("users", [
	where({ last_activity: moreThan(timeAgo("1h")) }),
	withRelations({ role: belongsTo("role") }),
	collapseBy("role", [
		compute({ count: count() })
	])
]);

// Update a user's last activity

update("users", [
	where({ id: userID }),
	set({ last_activity: now() })
]);

// Show latest threads in all categories except hidden threads and frontpage-hidden categories

function mostRecent(field) {
	return [
		first(),
		sortedBy(descending(field))
	];
}

select("threads", [
	define("category", belongsTo("category")),
	define("last_post", has("posts.thread", [
		mostRecent("created_at")
	])),
	where({
		visible: true,
		category: { visible_on_frontpage: true }
	}),
	sortedBy(descending("last_post.created_at"))
]);

// Get a thread with its (visible) posts

select("threads", [
	first(),
	where({ id: threadID }),
	withRelations({
		posts: has("posts.thread", [
			where({ visible: true }),
			startAt(offset),
			first(10)
		])
	})
]);

// Create a new thread

create("threads", [
	withRelations({ posts: has("posts.thread") }),
	set({
		title: title,
		user: userID,
		posts: [{
			user: userID,
			body: body
		}]
	})
]);

// Update the thread title

update("threads", [
	where({ id: threadID }),
	set({
		title: newTitle,
		updated_at: now()
	})
]);

// Create a new post

create("posts", {
	thread: threadID,
	user: userID,
	body: body
});

// Edit a post body

update("posts", [
	where({ id: postID }),
	set({
		body: newBody,
		updated_at: now()
	})
]);