35 lines
1.5 KiB
SQL
35 lines
1.5 KiB
SQL
DROP TABLE IF EXISTS "user";
|
|
|
|
-- DROP SEQUENCE IF EXISTS "user_user_id_seq";
|
|
-- CREATE SEQUENCE "user_user_id_seq";
|
|
|
|
|
|
CREATE TABLE "user" (
|
|
"user_id" int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, -- SERIAL PRIMARY KEY
|
|
"user_group_id" int check ("user_group_id" > 0) NOT NULL DEFAULT 1,
|
|
"site_id" int check ("site_id" > 0) NOT NULL DEFAULT 1,
|
|
"username" varchar(60) NOT NULL DEFAULT '',
|
|
"first_name" varchar(32) NOT NULL DEFAULT '',
|
|
"last_name" varchar(32) NOT NULL DEFAULT '',
|
|
"password" varchar(191) NOT NULL DEFAULT '',
|
|
"email" varchar(100) NOT NULL DEFAULT '',
|
|
"phone_number" varchar(32) NOT NULL DEFAULT '',
|
|
"url" varchar(100) NOT NULL DEFAULT '',
|
|
"status" int check ("status" >= 0) NOT NULL DEFAULT 0,
|
|
"display_name" varchar(250) NOT NULL DEFAULT '',
|
|
"avatar" varchar(250) NOT NULL DEFAULT '',
|
|
"cover" varchar(250) NOT NULL DEFAULT '',
|
|
"bio" varchar(250) NOT NULL DEFAULT '',
|
|
"token" varchar(32) NOT NULL DEFAULT '',
|
|
"secret" varchar(32) NOT NULL DEFAULT '',
|
|
"subscribe" smallint check ("status" >= 0) NOT NULL DEFAULT '0',
|
|
"last_ip" varchar(40) NOT NULL DEFAULT '',
|
|
"created_at" timestamp(0) NOT NULL DEFAULT now(),
|
|
"updated_at" timestamp(0) NOT NULL DEFAULT now()
|
|
-- PRIMARY KEY("user_id")
|
|
);
|
|
|
|
CREATE INDEX "user_username" ON "user" ("username");
|
|
CREATE INDEX "user_email" ON "user" ("email");
|
|
CREATE INDEX "user_created_at" ON "user" ("created_at");
|
|
SELECT setval('"user_user_id_seq"', 1, true); -- last inserted id by sample data |