ooorum

Ein simples Internetforum.

Gliederung

1. Demo
2. Datenbankentwurf
3. Implementierung

Demo

fooorum.vercel.app

Datenbankentwurf

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)
      

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
);

      

Implementierung

github.com/fooorum

Deklaration der Tabellen

        import { defineDb, defineTable, column, NOW, FALSE } from "astro:db";

type Id = ReturnType>;

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,
  },
});

      

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>[number];

      

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 = {
  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 = {
  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));
---


  
  


  {
    posts
      .flatMap((post) => [, ])
      .slice(0, -1)
  }


      

Vielen Dank für eure Aufmerksamkeit 🎉

Quellen