to-query-test.2.0.0.source-code.schema.sql Maven / Gradle / Ivy

Go to download
/*
 * Copyright © 2019-2024 Forb Yuan
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

SET DATABASE SQL SYNTAX MYS TRUE;

drop table t_user if exists;
create table t_user
(
  id bigint generated by default as identity (start with 1),
  email varchar(255),
  mobile varchar(255),
  nickname varchar(255),
  password varchar(255),
  user_level varchar(255),
  username varchar(255),
  memo varchar(255),
  valid boolean DEFAULT TRUE,
  create_time timestamp not null default CURRENT_TIMESTAMP,
  create_user_id bigint,
  update_time timestamp,
  update_user_id bigint,
  primary key (id)
);

drop table t_user_detail if exists;
create table t_user_detail (
  id bigint,
  address varchar(255),
  primary key (id)
);

drop table t_menu_01 if exists;
create table t_menu_01 (
  id integer generated by default as identity (start with 1),
  create_time timestamp,
  create_user_id bigint,
  update_time timestamp,
  update_user_id bigint,
  platform varchar(15),
  memo varchar(255),
  menu_name varchar(255),
  parent_id integer,
  valid boolean,
  primary key (id)
);

drop table t_menu if exists;
create table t_menu (
  id integer generated by default as identity (start with 1),
  create_time timestamp,
  create_user_id bigint,
  update_time timestamp,
  update_user_id bigint,
  platform varchar(15),
  memo varchar(255),
  menu_name varchar(255),
  parent_id integer,
  valid boolean,
  primary key (id)
);

drop table t_role if exists;
create table t_role(
  id int generated by default as identity (start with 1),
  role_name VARCHAR(100) not null,
  role_code VARCHAR(100) not null,
  valid boolean DEFAULT TRUE,
  create_time timestamp not null default CURRENT_TIMESTAMP,
  create_user_id bigint,
  update_time timestamp,
  update_user_id bigint,
  primary key (id)
);

drop table a_user_and_role if exists;
create table a_user_and_role (user_id bigint, role_id int, create_user_id bigint);
alter table a_user_and_role add constraint a_user_and_role_unique_constraint unique (user_id, role_id);

drop table t_perm if exists;
CREATE TABLE t_perm
(
  id        bigint generated BY DEFAULT AS IDENTITY (start WITH 1),
  perm_name  VARCHAR(100) not null,
  valid     boolean DEFAULT TRUE,
  primary key (id)
);

drop table a_role_and_perm if exists;
create table a_role_and_perm (role_id int, perm_id int);
alter table a_role_and_perm add constraint a_role_and_perm_unique_constraint unique (role_id, perm_id);

drop table a_perm_and_menu if exists;
create table a_perm_and_menu (perm_id int, menu_id int);
alter table a_perm_and_menu add constraint a_perm_and_menu_unique_constraint unique (perm_id, menu_id);