ooorum

Entity-Relationship-Modell

Relationales Modell

        
Forum(#id, name, description, createdAt)
User(#id, name, password, description, isAdmin, createdAt)
 
Vote(↑#postId, ↑#commentId, ↑#userId, score)
 
Comment(#id, description, deleted, createdAt, ↑userId, ↑postId, ↑parentId)
Post(#id, title, description, deleted, createdAt, ↑userId, ↑forumId, ↑embedId)
 
Embed(#id, url, title, description, ↑mediaId)
Media(#id, url, ty‎pe, alt, width, height)
      
Forum(#id, name, description, createdAt)
User(#id, name, password, description, isAdmin, createdAt)
 
Vote(↑#postId, ↑#commentId, ↑#userId, score)
 
Comment(#id, description, deleted, createdAt, ↑userId, ↑postId, ↑parentId)
Post(#id, title, description, deleted, createdAt, ↑userId, ↑forumId, ↑embedId)
 
Embed(#id, url, title, description, ↑mediaId)
Media(#id, url, ty‎pe, alt, width, height)
Forum(#id, name, description, createdAt)
User(#id, name, password, description, isAdmin, createdAt)
 
Vote(↑#postId, ↑#commentId, ↑#userId, score)
 
Comment(#id, description, deleted, createdAt, ↑userId, ↑postId, ↑parentId)
Post(#id, title, description, deleted, createdAt, ↑userId, ↑forumId, ↑embedId)
 
Embed(#id, url, title, description, ↑mediaId)
Media(#id, url, ty‎pe, alt, width, height)
Forum(#id, name, description, createdAt)
User(#id, name, password, description, isAdmin, createdAt)
 
Vote(↑#postId, ↑#commentId, ↑#userId, score)
 
Comment(#id, description, deleted, createdAt, ↑userId, ↑postId, ↑parentId)
Post(#id, title, description, deleted, createdAt, ↑userId, ↑forumId, ↑embedId)
 
Embed(#id, url, title, description, ↑mediaId)
Media(#id, url, ty‎pe, alt, width, height)

SQL-Setup-Script

        
CREATE TABLE Forum 
(
    id integer PRIMARY KEY,
    name text NOT NULL UNIQUE,
    description text,
    createdAt text NOT NULL DEFAULT CURRENT_TIMESTAMP
);
 
CREATE TABLE User 
(
    id integer PRIMARY KEY,
    name text NOT NULL UNIQUE,
    description text,
    password text NOT NULL,
    createdAt text NOT NULL DEFAULT CURRENT_TIMESTAMP,
    isAdmin integer NOT NULL DEFAULT FALSE
);
 
CREATE TABLE Post 
(
    id integer PRIMARY KEY,
    title text NOT NULL,
    description text NOT NULL,
    embedId integer REFERENCES Embed (id),
    userId integer NOT NULL REFERENCES User (id),
    forumId integer NOT NULL REFERENCES Forum (id),
    createdAt text NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted integer NOT NULL DEFAULT FALSE
);
 
CREATE TABLE Comment 
(
    id integer PRIMARY KEY,
    description text NOT NULL,
    userId integer NOT NULL REFERENCES User (id),
    postId integer NOT NULL REFERENCES Post (id),
    parentId integer REFERENCES Comment (id),
    createdAt text NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted integer NOT NULL DEFAULT FALSE
);
 
CREATE TABLE Embed 
(
    id integer PRIMARY KEY,
    url text NOT NULL UNIQUE,
    title text,
    description text,
    mediaId integer REFERENCES Media (id)
);
 
CREATE TABLE Media 
(
    id integer PRIMARY KEY,
    url text NOT NULL UNIQUE,
    type text NOT NULL,
    alt text,
    width integer,
    height integer
);
 
CREATE TABLE Vote 
(
    userId integer NOT NULL REFERENCES User (id),
    postId integer REFERENCES Post (id),
    commentId integer REFERENCES Comment (id),
    score integer NOT NULL
);
      
CREATE TABLE Forum 
(
    id integer PRIMARY KEY,
    name text NOT NULL UNIQUE,
    description text,
    createdAt text NOT NULL DEFAULT CURRENT_TIMESTAMP
);
 
CREATE TABLE User 
(
    id integer PRIMARY KEY,
    name text NOT NULL UNIQUE,
    description text,
    password text NOT NULL,
    createdAt text NOT NULL DEFAULT CURRENT_TIMESTAMP,
    isAdmin integer NOT NULL DEFAULT FALSE
);
 
CREATE TABLE Post 
(
    id integer PRIMARY KEY,
    title text NOT NULL,
    description text NOT NULL,
    embedId integer REFERENCES Embed (id),
    userId integer NOT NULL REFERENCES User (id),
    forumId integer NOT NULL REFERENCES Forum (id),
    createdAt text NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted integer NOT NULL DEFAULT FALSE
);
 
CREATE TABLE Comment 
(
    id integer PRIMARY KEY,
    description text NOT NULL,
    userId integer NOT NULL REFERENCES User (id),
    postId integer NOT NULL REFERENCES Post (id),
    parentId integer REFERENCES Comment (id),
    createdAt text NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted integer NOT NULL DEFAULT FALSE
);
 
CREATE TABLE Embed 
(
    id integer PRIMARY KEY,
    url text NOT NULL UNIQUE,
    title text,
    description text,
    mediaId integer REFERENCES Media (id)
);
 
CREATE TABLE Media 
(
    id integer PRIMARY KEY,
    url text NOT NULL UNIQUE,
    type text NOT NULL,
    alt text,
    width integer,
    height integer
);
 
CREATE TABLE Vote 
(
    userId integer NOT NULL REFERENCES User (id),
    postId integer REFERENCES Post (id),
    commentId integer REFERENCES Comment (id),
    score integer NOT NULL
);

Deklaration der Tabellen

        
import { defineDb, defineTable, column, NOW, FALSE } from "astro:db";
 
type Id = ReturnType<typeof column.number<{="" primarykey:="" true="" }="">>;
 
const Forum = defineTable({
  columns: {
    id: column.number({ primaryKey: true }),
    name: column.text({ unique: true }),
    description: column.text({ optional: true }),
    createdAt: column.date({ default: NOW }),
  },
});
 
const User = defineTable({
  columns: {
    id: column.number({ primaryKey: true }),
    name: column.text({ unique: true }),
    description: column.text({ optional: true }),
    password: column.text(),
    createdAt: column.date({ default: NOW }),
    isAdmin: column.boolean({ default: FALSE }),
  },
});
 
const Session = defineTable({
  columns: {
    id: column.text({
      primaryKey: true,
    }),
    expiresAt: column.date(),
    userId: column.number({
      references: () => User.columns.id,
    }),
  },
});
 
const Member = defineTable({
  columns: {
    userId: column.number({ references: () => User.columns.id }),
    forumId: column.number({ references: () => Forum.columns.id }),
    createdAt: column.date({ default: NOW }),
    isAdmin: column.boolean({ default: FALSE }),
  },
  indexes: [{ on: ["userId", "forumId"], unique: true, name: "memberId" }],
});
 
const Post = defineTable({
  columns: {
    id: column.number({ primaryKey: true }),
    title: column.text({ optional: true }),
    description: column.text(),
    attachementUrl: column.text({ optional: true }),
    userId: column.number({ references: () => User.columns.id }),
    forumId: column.number({ references: () => Forum.columns.id }),
    parentId: column.number({
      references: (): Id => Post.columns.id,
      optional: true,
    }),
    createdAt: column.date({ default: NOW }),
    isDeleted: column.boolean({ default: FALSE }),
  },
});
 
const BaseVote = defineTable({
  columns: {
    userId: column.number({ references: () => User.columns.id }),
    postId: column.number({ references: () => Post.columns.id }),
  },
  indexes: [{ on: ["userId", "postId"], unique: true, name: "voteId" }],
});
 
const Vote = defineTable({
  columns: {
    ...BaseVote.columns,
    score: column.number(),
  },
});
 
export default defineDb({
  tables: {
    Forum,
    User,
    Session,
    Member,
    Post,
    Vote,
  },
});
</typeof>
      
import { defineDb, defineTable, column, NOW, FALSE } from "astro:db";
 
type Id = ReturnType<typeof column.number<{="" primarykey:="" true="" }="">>;
 
const Forum = defineTable({
  columns: {
    id: column.number({ primaryKey: true }),
    name: column.text({ unique: true }),
    description: column.text({ optional: true }),
    createdAt: column.date({ default: NOW }),
  },
});
 
const User = defineTable({
  columns: {
    id: column.number({ primaryKey: true }),
    name: column.text({ unique: true }),
    description: column.text({ optional: true }),
    password: column.text(),
    createdAt: column.date({ default: NOW }),
    isAdmin: column.boolean({ default: FALSE }),
  },
});
 
const Session = defineTable({
  columns: {
    id: column.text({
      primaryKey: true,
    }),
    expiresAt: column.date(),
    userId: column.number({
      references: () => User.columns.id,
    }),
  },
});
 
const Member = defineTable({
  columns: {
    userId: column.number({ references: () => User.columns.id }),
    forumId: column.number({ references: () => Forum.columns.id }),
    createdAt: column.date({ default: NOW }),
    isAdmin: column.boolean({ default: FALSE }),
  },
  indexes: [{ on: ["userId", "forumId"], unique: true, name: "memberId" }],
});
 
const Post = defineTable({
  columns: {
    id: column.number({ primaryKey: true }),
    title: column.text({ optional: true }),
    description: column.text(),
    attachementUrl: column.text({ optional: true }),
    userId: column.number({ references: () => User.columns.id }),
    forumId: column.number({ references: () => Forum.columns.id }),
    parentId: column.number({
      references: (): Id => Post.columns.id,
      optional: true,
    }),
    createdAt: column.date({ default: NOW }),
    isDeleted: column.boolean({ default: FALSE }),
  },
});
 
const BaseVote = defineTable({
  columns: {
    userId: column.number({ references: () => User.columns.id }),
    postId: column.number({ references: () => Post.columns.id }),
  },
  indexes: [{ on: ["userId", "postId"], unique: true, name: "voteId" }],
});
 
const Vote = defineTable({
  columns: {
    ...BaseVote.columns,
    score: column.number(),
  },
});
 
export default defineDb({
  tables: {
    Forum,
    User,
    Session,
    Member,
    Post,
    Vote,
  },
});
</typeof>
import { defineDb, defineTable, column, NOW, FALSE } from "astro:db";
 
type Id = ReturnType<typeof column.number<{="" primarykey:="" true="" }="">>;
 
const Forum = defineTable({
  columns: {
    id: column.number({ primaryKey: true }),
    name: column.text({ unique: true }),
    description: column.text({ optional: true }),
    createdAt: column.date({ default: NOW }),
  },
});
 
const User = defineTable({
  columns: {
    id: column.number({ primaryKey: true }),
    name: column.text({ unique: true }),
    description: column.text({ optional: true }),
    password: column.text(),
    createdAt: column.date({ default: NOW }),
    isAdmin: column.boolean({ default: FALSE }),
  },
});
 
const Session = defineTable({
  columns: {
    id: column.text({
      primaryKey: true,
    }),
    expiresAt: column.date(),
    userId: column.number({
      references: () => User.columns.id,
    }),
  },
});
 
const Member = defineTable({
  columns: {
    userId: column.number({ references: () => User.columns.id }),
    forumId: column.number({ references: () => Forum.columns.id }),
    createdAt: column.date({ default: NOW }),
    isAdmin: column.boolean({ default: FALSE }),
  },
  indexes: [{ on: ["userId", "forumId"], unique: true, name: "memberId" }],
});
 
const Post = defineTable({
  columns: {
    id: column.number({ primaryKey: true }),
    title: column.text({ optional: true }),
    description: column.text(),
    attachementUrl: column.text({ optional: true }),
    userId: column.number({ references: () => User.columns.id }),
    forumId: column.number({ references: () => Forum.columns.id }),
    parentId: column.number({
      references: (): Id => Post.columns.id,
      optional: true,
    }),
    createdAt: column.date({ default: NOW }),
    isDeleted: column.boolean({ default: FALSE }),
  },
});
 
const BaseVote = defineTable({
  columns: {
    userId: column.number({ references: () => User.columns.id }),
    postId: column.number({ references: () => Post.columns.id }),
  },
  indexes: [{ on: ["userId", "postId"], unique: true, name: "voteId" }],
});
 
const Vote = defineTable({
  columns: {
    ...BaseVote.columns,
    score: column.number(),
  },
});
 
export default defineDb({
  tables: {
    Forum,
    User,
    Session,
    Member,
    Post,
    Vote,
  },
});
</typeof>

Abfragen an die Datenbank

        
import {
  db,
  alias,
  countDistinct,
  eq,
  and,
  User,
  Forum,
  Post,
  Vote,
} from "astro:db";
 
export const Upvote = alias(Vote, "upvote");
export const Downvote = alias(Vote, "downvote");
export const Voted = alias(Vote, "voted");
export const Comment = alias(Post, "comment");
 
export function selectPosts(options: { userId?: number }) {
  return db
    .select({
      postTitle: Post.title,
      postDescription: Post.description,
      postId: Post.id,
      isDeleted: Post.isDeleted,
      attachementUrl: Post.attachementUrl,
      userName: User.name,
      userId: User.id,
      forumName: Forum.name,
      forumId: Forum.id,
      upvotes: countDistinct(Upvote.userId),
      downvotes: countDistinct(Downvote.userId),
      votedScore: Voted.score,
      comments: countDistinct(Comment.id),
    })
    .from(Post)
    .innerJoin(User, eq(Post.userId, User.id))
    .innerJoin(Forum, eq(Post.forumId, Forum.id))
    .leftJoin(Upvote, and(eq(Upvote.postId, Post.id), eq(Upvote.score, 1)))
    .leftJoin(
      Downvote,
      and(eq(Downvote.postId, Post.id), eq(Downvote.score, -1)),
    )
    .leftJoin(
      Voted,
      and(eq(Voted.postId, Post.id), eq(Voted.userId, options.userId ?? -1)),
    )
    .leftJoin(Comment, eq(Comment.parentId, Post.id))
    .groupBy(Post.id);
}
 
export type PostSelection = Awaited<returntype<typeof selectposts="">>[number];
</returntype<typeof>
      
import {
  db,
  alias,
  countDistinct,
  eq,
  and,
  User,
  Forum,
  Post,
  Vote,
} from "astro:db";
 
export const Upvote = alias(Vote, "upvote");
export const Downvote = alias(Vote, "downvote");
export const Voted = alias(Vote, "voted");
export const Comment = alias(Post, "comment");
 
export function selectPosts(options: { userId?: number }) {
  return db
    .select({
      postTitle: Post.title,
      postDescription: Post.description,
      postId: Post.id,
      isDeleted: Post.isDeleted,
      attachementUrl: Post.attachementUrl,
      userName: User.name,
      userId: User.id,
      forumName: Forum.name,
      forumId: Forum.id,
      upvotes: countDistinct(Upvote.userId),
      downvotes: countDistinct(Downvote.userId),
      votedScore: Voted.score,
      comments: countDistinct(Comment.id),
    })
    .from(Post)
    .innerJoin(User, eq(Post.userId, User.id))
    .innerJoin(Forum, eq(Post.forumId, Forum.id))
    .leftJoin(Upvote, and(eq(Upvote.postId, Post.id), eq(Upvote.score, 1)))
    .leftJoin(
      Downvote,
      and(eq(Downvote.postId, Post.id), eq(Downvote.score, -1)),
    )
    .leftJoin(
      Voted,
      and(eq(Voted.postId, Post.id), eq(Voted.userId, options.userId ?? -1)),
    )
    .leftJoin(Comment, eq(Comment.parentId, Post.id))
    .groupBy(Post.id);
}
 
export type PostSelection = Awaited<returntype<typeof selectposts="">>[number];
</returntype<typeof>

Anzeigen der Daten

        
---
import Stack from "@components/feeds/Stack.astro";
import Selections from "@components/feeds/Selections.astro";
import Select, {
  getSelected,
  type Selection,
} from "@components/feeds/Select.astro";
import Separator from "@components/feeds/Separator.astro";
import {
  and,
  eq,
  sum,
  isNull,
  asc,
  desc,
  Post,
  Vote,
  countDistinct,
  TRUE,
  ne,
} from "astro:db";
import Card from "@components/cards/Post.astro";
import { Comment, selectPosts } from "@lib/db";
import type { SQLWrapper } from "drizzle-orm/sql";
 
interface Props {
  forum?: number;
  user?: number;
}
 
const { forum, user } = Astro.props;
 
const filters = [isNull(Post.parentId), ne(Post.isDeleted, TRUE)];
if (forum !== undefined) filters.push(eq(Post.forumId, forum));
if (user !== undefined) filters.push(eq(Post.userId, user));
 
const sortSelection: Selection<sqlwrapper> = {
  param: "sort",
  default: "created",
  options: [
    { name: "Erstellt", id: "created", value: Post.createdAt },
    { name: "Titel", id: "title", value: Post.title },
    { name: "Index", id: "id", value: Post.id },
    { name: "Kommentare", id: "comments", value: countDistinct(Comment.id) },
    { name: "Bewertungen", id: "votes", value: sum(Vote.score) },
  ],
};
 
const orderSelection: Selection<typeof asc="" |="" typeof="" desc=""> = {
  param: "order",
  default: "desc",
  options: [
    { name: "Aufsteigend", id: "asc", value: asc },
    { name: "Absteigend", id: "desc", value: desc },
  ],
};
 
const sort = getSelected(sortSelection, Astro.url).value;
const order = getSelected(orderSelection, Astro.url).value;
 
const posts = await selectPosts({ userId: Astro.locals.user?.id })
  .where(and(...filters))
  .orderBy(order(sort));
---
 
<selections>
  <select {...sortselection}="">
  </select>
</selections>
<stack>
  {
    posts
      .flatMap((post) => [<card {...post}="">, <separator id="{post.postId}">])
      .slice(0, -1)
  }
</separator></card></stack>
</typeof></sqlwrapper>
      

Quellen