一款好用的在线ORM编辑器

https://editor.ponyorm.com/

1、Python代码:

Editing diagram xww2008/blog

Explore
xww2008
Log out
from datetime import datetime
from pony.orm import *


db = Database()


class User(db.Entity):
    id = PrimaryKey(int, auto=True)
    username = Required(str)
    password = Required(str)
    email = Required(str)
    dt_registered = Required(datetime)
    dt_last_visit = Required(datetime)
    posts = Set('Post')
    reactions = Set('Reaction')


class Post(db.Entity):
    id = PrimaryKey(int, auto=True)
    author = Required(User)
    body = Required(str)
    created_at = Required(datetime)
    updated_at = Optional(datetime)
    responses = Set('Comment')
    reactions = Set('Reaction')


class Tag(db.Entity):
    id = PrimaryKey(int, auto=True)
    name = Required(str, unique=True)
    topics = Set('Topic')


class Topic(Post):
    title = Required(str)
    tags = Set(Tag)
    comments = Set('Comment')


class Comment(Post):
    parent = Required(Post)
    topic = Required(Topic)


class Reaction(db.Entity):
    id = PrimaryKey(int, auto=True)
    user = Required(User)
    post = Required(Post)
    value = Required(int)
    dt = Required(datetime)



db.generate_mapping()

2、MySQL代码:

CREATE TABLE `tag` (
  `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE `user` (
  `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
  `username` VARCHAR(255) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `dt_registered` DATETIME NOT NULL,
  `dt_last_visit` DATETIME NOT NULL
);

CREATE TABLE `post` (
  `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
  `author` INTEGER NOT NULL,
  `body` VARCHAR(255) NOT NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME,
  `classtype` VARCHAR(255) NOT NULL,
  `title` VARCHAR(255),
  `parent` INTEGER,
  `topic` INTEGER
);

CREATE INDEX `idx_post__author` ON `post` (`author`);

CREATE INDEX `idx_post__parent` ON `post` (`parent`);

CREATE INDEX `idx_post__topic` ON `post` (`topic`);

ALTER TABLE `post` ADD CONSTRAINT `fk_post__author` FOREIGN KEY (`author`) REFERENCES `user` (`id`) ON DELETE CASCADE;

ALTER TABLE `post` ADD CONSTRAINT `fk_post__parent` FOREIGN KEY (`parent`) REFERENCES `post` (`id`) ON DELETE CASCADE;

ALTER TABLE `post` ADD CONSTRAINT `fk_post__topic` FOREIGN KEY (`topic`) REFERENCES `post` (`id`) ON DELETE CASCADE;

CREATE TABLE `reaction` (
  `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
  `user` INTEGER NOT NULL,
  `post` INTEGER NOT NULL,
  `value` INTEGER NOT NULL,
  `dt` DATETIME NOT NULL
);

CREATE INDEX `idx_reaction__post` ON `reaction` (`post`);

CREATE INDEX `idx_reaction__user` ON `reaction` (`user`);

ALTER TABLE `reaction` ADD CONSTRAINT `fk_reaction__post` FOREIGN KEY (`post`) REFERENCES `post` (`id`) ON DELETE CASCADE;

ALTER TABLE `reaction` ADD CONSTRAINT `fk_reaction__user` FOREIGN KEY (`user`) REFERENCES `user` (`id`) ON DELETE CASCADE;

CREATE TABLE `tag_topic` (
  `tag` INTEGER NOT NULL,
  `topic` INTEGER NOT NULL,
  PRIMARY KEY (`tag`, `topic`)
);

CREATE INDEX `idx_tag_topic` ON `tag_topic` (`topic`);

ALTER TABLE `tag_topic` ADD CONSTRAINT `fk_tag_topic__tag` FOREIGN KEY (`tag`) REFERENCES `tag` (`id`);

ALTER TABLE `tag_topic` ADD CONSTRAINT `fk_tag_topic__topic` FOREIGN KEY (`topic`) REFERENCES `post` (`id`)

3、Oracle代码

CREATE TABLE "TAG" (
  "ID" NUMBER(10) PRIMARY KEY,
  "NAME" VARCHAR2(1000 CHAR) UNIQUE NOT NULL
);

CREATE SEQUENCE "TAG_SEQ" NOCACHE;

CREATE TRIGGER "TAG_BI"
  BEFORE INSERT ON "TAG"
  FOR EACH ROW
BEGIN
  IF :NEW."ID" IS NULL THEN
    SELECT "TAG_SEQ".NEXTVAL INTO :NEW."ID" FROM DUAL;
  END IF;
END;;

CREATE TABLE "USER" (
  "ID" NUMBER(10) PRIMARY KEY,
  "USERNAME" VARCHAR2(1000 CHAR) NOT NULL,
  "PASSWORD" VARCHAR2(1000 CHAR) NOT NULL,
  "EMAIL" VARCHAR2(1000 CHAR) NOT NULL,
  "DT_REGISTERED" TIMESTAMP NOT NULL,
  "DT_LAST_VISIT" TIMESTAMP NOT NULL
);

CREATE SEQUENCE "USER_SEQ" NOCACHE;

CREATE TRIGGER "USER_BI"
  BEFORE INSERT ON "USER"
  FOR EACH ROW
BEGIN
  IF :NEW."ID" IS NULL THEN
    SELECT "USER_SEQ".NEXTVAL INTO :NEW."ID" FROM DUAL;
  END IF;
END;;

CREATE TABLE "POST" (
  "ID" NUMBER(10) PRIMARY KEY,
  "AUTHOR" NUMBER(10) NOT NULL,
  "BODY" VARCHAR2(1000 CHAR) NOT NULL,
  "CREATED_AT" TIMESTAMP NOT NULL,
  "UPDATED_AT" TIMESTAMP,
  "classtype" VARCHAR2(1000 CHAR) NOT NULL,
  "TITLE" VARCHAR2(1000 CHAR),
  "PARENT" NUMBER(10),
  "TOPIC" NUMBER(10)
);

CREATE INDEX "IDX_POST__AUTHOR" ON "POST" ("AUTHOR");

CREATE INDEX "IDX_POST__PARENT" ON "POST" ("PARENT");

CREATE INDEX "IDX_POST__TOPIC" ON "POST" ("TOPIC");

ALTER TABLE "POST" ADD CONSTRAINT "FK_POST__AUTHOR" FOREIGN KEY ("AUTHOR") REFERENCES "USER" ("ID") ON DELETE CASCADE;

ALTER TABLE "POST" ADD CONSTRAINT "FK_POST__PARENT" FOREIGN KEY ("PARENT") REFERENCES "POST" ("ID") ON DELETE CASCADE;

ALTER TABLE "POST" ADD CONSTRAINT "FK_POST__TOPIC" FOREIGN KEY ("TOPIC") REFERENCES "POST" ("ID") ON DELETE CASCADE;

CREATE SEQUENCE "POST_SEQ" NOCACHE;

CREATE TRIGGER "POST_BI"
  BEFORE INSERT ON "POST"
  FOR EACH ROW
BEGIN
  IF :NEW."ID" IS NULL THEN
    SELECT "POST_SEQ".NEXTVAL INTO :NEW."ID" FROM DUAL;
  END IF;
END;;

CREATE TABLE "REACTION" (
  "ID" NUMBER(10) PRIMARY KEY,
  "USER" NUMBER(10) NOT NULL,
  "POST" NUMBER(10) NOT NULL,
  "VALUE" NUMBER(10) NOT NULL,
  "DT" TIMESTAMP NOT NULL
);

CREATE INDEX "IDX_REACTION__POST" ON "REACTION" ("POST");

CREATE INDEX "IDX_REACTION__USER" ON "REACTION" ("USER");

ALTER TABLE "REACTION" ADD CONSTRAINT "FK_REACTION__POST" FOREIGN KEY ("POST") REFERENCES "POST" ("ID") ON DELETE CASCADE;

ALTER TABLE "REACTION" ADD CONSTRAINT "FK_REACTION__USER" FOREIGN KEY ("USER") REFERENCES "USER" ("ID") ON DELETE CASCADE;

CREATE SEQUENCE "REACTION_SEQ" NOCACHE;

CREATE TRIGGER "REACTION_BI"
  BEFORE INSERT ON "REACTION"
  FOR EACH ROW
BEGIN
  IF :NEW."ID" IS NULL THEN
    SELECT "REACTION_SEQ".NEXTVAL INTO :NEW."ID" FROM DUAL;
  END IF;
END;;

CREATE TABLE "TAG_TOPIC" (
  "TAG" NUMBER(10) NOT NULL,
  "TOPIC" NUMBER(10) NOT NULL,
  PRIMARY KEY ("TAG", "TOPIC")
);

CREATE INDEX "IDX_TAG_TOPIC" ON "TAG_TOPIC" ("TOPIC");

ALTER TABLE "TAG_TOPIC" ADD CONSTRAINT "FK_TAG_TOPIC__TAG" FOREIGN KEY ("TAG") REFERENCES "TAG" ("ID");

ALTER TABLE "TAG_TOPIC" ADD CONSTRAINT "FK_TAG_TOPIC__TOPIC" FOREIGN KEY ("TOPIC") REFERENCES "POST" ("ID")
点赞

发表回复

电子邮件地址不会被公开。必填项已用 * 标注