suhan1996
83 lines
2.7 KiB
SQL
83 lines
2.7 KiB
SQL
create table user
|
|
(
|
|
uid integer primary key autoincrement not null,
|
|
name text collate nocase not null,
|
|
password text,
|
|
email text collate nocase,
|
|
gender integer not null,
|
|
language text not null,
|
|
is_admin boolean not null default false,
|
|
create_by text not null,
|
|
avatar_updated_at timestamp not null default '1970-01-01 00:00:00',
|
|
created_at timestamp not null default current_timestamp,
|
|
updated_at timestamp not null default current_timestamp
|
|
);
|
|
|
|
create unique index user_name on user (name);
|
|
create unique index user_email on user (email);
|
|
|
|
create table google_auth
|
|
(
|
|
email text primary key not null,
|
|
uid integer not null,
|
|
foreign key (uid) references user (uid) on delete cascade
|
|
);
|
|
|
|
create unique index google_auth_uid on google_auth (uid);
|
|
|
|
create table openid_connect
|
|
(
|
|
issuer text not null,
|
|
subject text not null,
|
|
uid integer not null,
|
|
primary key (issuer, subject),
|
|
foreign key (uid) references user (uid) on delete cascade
|
|
);
|
|
|
|
create unique index openid_connect_uid on openid_connect (uid);
|
|
|
|
create table refresh_token
|
|
(
|
|
uid integer not null,
|
|
device text not null,
|
|
token text not null,
|
|
created_at timestamp not null default current_timestamp,
|
|
updated_at timestamp not null default current_timestamp,
|
|
primary key (uid, device),
|
|
foreign key (uid) references user (uid) on delete cascade
|
|
);
|
|
|
|
create table device
|
|
(
|
|
uid integer not null,
|
|
device text not null,
|
|
device_token text,
|
|
created_at timestamp not null default current_timestamp,
|
|
updated_at timestamp not null default current_timestamp,
|
|
primary key (uid, device),
|
|
foreign key (uid) references user (uid) on delete cascade
|
|
);
|
|
|
|
create table `group`
|
|
(
|
|
gid integer primary key autoincrement not null,
|
|
name text not null,
|
|
owner integer,
|
|
is_public bool not null default false,
|
|
description text not null default '',
|
|
created_at timestamp not null default current_timestamp,
|
|
updated_at timestamp not null default current_timestamp
|
|
);
|
|
|
|
create table group_user
|
|
(
|
|
id integer primary key autoincrement not null,
|
|
gid integer not null,
|
|
uid integer not null,
|
|
foreign key (gid) references `group` (gid) on delete cascade,
|
|
foreign key (uid) references user (uid) on delete cascade
|
|
);
|
|
|
|
create unique index group_user_gid_uid on group_user (gid, uid);
|
|
create index group_user_gid on group_user (gid);
|