PostgreSQL 迁移 - geekapk-r/ServerR GitHub Wiki

用户 PSQL

CREATE TABLE users(
    id SmallSerial PRIMARY KEY,
    simple_name VarChar NOT Null,
    avatar_url VarChar,
    user_name VarChar NOT Null,
    alias VarChar,
    github VarChar,
    bio VarChar NOT Null DEFAULT '',
    dev_bio VarChar,
    created_at TimeStamp NOT Null DEFAULT now(),
    online_at TimeStamp NOT Null DEFAULT now(),
    followers_num SmallInt NOT Null DEFAULT 0,
    enabled Boolean NOT Null DEFAULT true
)

DROP TABLE users

分类 PSQL

CREATE TABLE categories(
    id SmallSerial PRIMARY KEY,
    category_name VarChar NOT Null,
    parent_category SmallInt
)

DROP TABLE categories

应用 PSQL

CREATE TABLE apps(
    id SmallSerial PRIMARY KEY,
    author_user SmallInt NOT Null,
    category SmallInt NOT Null,
    package_name VarChar,
    app_name VarChar NOT Null,
    alias VarChar,
    icon_url VarChar,
    app_description VarChar NOT Null DEFAULT '',
    visualizer VarChar,
    button_text VarChar,
    special VarChar,
    previews VarChar,
    app_permissions VarChar,
    size Integer NOT Null DEFAULT 0,
    created_at TimeStamp NOT Null DEFAULT now(),
    updated_at TimeStamp NOT Null DEFAULT now(),
    stars_num SmallInt NOT Null DEFAULT 0,
    comments_num Integer NOT Null DEFAULT 0
)

DROP TABLE apps

评论 PSQL

CREATE TABLE comments(
    id Serial PRIMARY KEY,
    author_user SmallInt NOT Null,
    app SmallInt NOT Null,
    reply_comment Integer,
    content VarChar NOT Null,
    stars_num SmallInt NOT Null DEFAULT 0,
    replies_num Integer NOT Null DEFAULT 0,
    created_at TimeStamp NOT Null DEFAULT now()
    updated_at TimeStamp
)

DROP TABLE comments

跟随 PSQL

CREATE TABLE follow(
    user SmallInt NOT Null,
    followed_user SmallInt NOT Null
)

DROP TABLE follow

密码 PSQL

CREATE TABLE _security(
    user SmallInt PRIMARY KEY,
    metapass VarChar NOT Null,
    passhash VarChar
)

DROP TABLE _security

更新 PSQL

CREATE TABLE updates(
    app SmallInt NOT Null,
    version_name VarChar NOT Null,
    reversion SmallInt NOT Null,
    install_url VarChar NOT Null,
    updates VarChar NOT Null,
    api_min SmallInt,
    api_target SmallInt
)

DROP TABLE updates

星标 PSQL

CREATE TABLE stars(
    user SmallInt NOT Null,
    app SmallInt NOT Null
)

DROP TABLE stars

评论星标 PSQL

CREATE TABLE comment_stars(
    user SmallInt NOT Null,
    comment Integer NOT Null
)

DROP TABLE comment_stars

时间线 PSQL

CREATE TABLE timelines(
    user SmallInt NOT Null,
    created_at TimeStamp NOT Null DEFAULT now(),
    line_type SmallInt NOT Null,
    line_data Integer NOT Null
)

DROP TABLE timelines

通知 PSQL

CREATE TABLE notifications(
    user SmallInt NOT Null,
    created_at TimeStamp NOT Null DEFAULT now(),
    notification_type SmallInt NOT Null,
    notification_data Integer NOT Null,
    enabled Boolean NOT Null DEFAULT false
)

DROP TABLE notifications