forum.js

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

createTable("permissions", {
	role: linkTo("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(),
	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: linkTo("users.id"),
	visible_on_frontpage: [ boolean(), defaultTo(true) ]
});

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

createTable("posts", {
	thread: linkTo("threads.id"),
	user: linkTo("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 all its 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()
	})
]);