mariadb

package
v0.0.0-...-a175cf1 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Jan 24, 2022 License: MIT Imports: 9 Imported by: 0

README

1. MariaDB

Refer:
https://hub.docker.com/_/mariadb
https://zhuanlan.zhihu.com/p/97035035

1.1. docker

docker pull mariadb
docker network create hfcms-mariadb
docker run --detach \
--publish 3306:3306 \
--env MARIADB_ROOT_PASSWORD=my-secret-pw \
--network hfcms-mariadb \
--name hfcms-mariadb \
mariadb:latest

docker exec -it hfcms-mariadb mysql -u root -pmy-secret-pw

MARIADB_ROOT_PASSWORD=my-secret-pw

1.2 mariadb

  1. Change root password:
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '[newpassword]';
  1. Creating database dumps
$ docker exec hfcms-mariadb sh -c 'exec mysqldump -uroot -p"$MARIADB_ROOT_PASSWORD" hfcms_users' > ./hfcms_users.sql
  1. Restoring data from dump files
$ docker exec -i hfcms-mariadb sh -c 'exec mysql -uroot -p"$MARIADB_ROOT_PASSWORD" hfcms_users' < ./hfcms_users.sql
  1. Create tables:
    Database
DROP database hfcms_users;
CREATE database hfcms_users;
CREATE USER 'hfcms_users_user'@'%' IDENTIFIED BY 'hfcms_users_user_passwd';
GRANT ALL PRIVILEGES ON hfcms_users.* TO 'hfcms_users_user'@'%';
FLUSH PRIVILEGES;
USE hfcms_users;

Users

CREATE TABLE IF NOT EXISTS users (
  id int(10) NOT NULL AUTO_INCREMENT,
  username VARCHAR(255) UNIQUE,
  password VARCHAR(255),
  realname VARCHAR(255),
  nickname VARCHAR(255),
  avatar_url VARCHAR(255),
  phone VARCHAR(11),
  user_ip INT(4) UNSIGNED,
  state TINYINT(1) COMMENT 'user state: 0=normal, 1=disable',
  deleted TINYINT(1) DEFAULT 0 COMMENT 'soft deleted: 0=undelete,1=deleted',
  create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

Departments

CREATE TABLE IF NOT EXISTS departments (
  id int(10) NOT NULL AUTO_INCREMENT,
  parent_id INT(10) DEFAULT NULL,
  code VARCHAR(255) UNIQUE,
  name VARCHAR(255),
  description VARCHAR(255),
  state TINYINT(1) COMMENT 'user state: 0=normal, 1=disable',
  deleted TINYINT(1) DEFAULT 0 COMMENT 'soft deleted: 0=undelete,1=deleted',
  update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

Department Users

CREATE TABLE IF NOT EXISTS department_users (
  id int(10) NOT NULL AUTO_INCREMENT,
  department_id INT(10),
  user_id INT(10),
  state TINYINT(1) COMMENT 'user state: 0=normal, 1=disable',
  deleted TINYINT(1) DEFAULT 0 COMMENT 'soft deleted: 0=undelete,1=deleted',
  update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

Roles

CREATE TABLE IF NOT EXISTS roles (
  id int(10) NOT NULL AUTO_INCREMENT,
  parent_id INT(10) DEFAULT NULL,
  code VARCHAR(255) UNIQUE,
  name VARCHAR(255),
  description VARCHAR(255),
  state TINYINT(1) COMMENT 'user state: 0=normal, 1=disable',
  deleted TINYINT(1) DEFAULT 0 COMMENT 'soft deleted: 0=undelete,1=deleted',
  update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

Role Users

CREATE TABLE IF NOT EXISTS role_users (
  id int(10) NOT NULL AUTO_INCREMENT,
  role_id INT(10),
  user_id INT(10),
  state TINYINT(1) COMMENT 'user state: 0=normal, 1=disable',
  deleted TINYINT(1) DEFAULT 0 COMMENT 'soft deleted: 0=undelete,1=deleted',
  update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

Permissions

CREATE TABLE IF NOT EXISTS permissions (
  id int(10) NOT NULL AUTO_INCREMENT,
  parent_id INT(10) DEFAULT NULL,
  code VARCHAR(255) UNIQUE,
  name VARCHAR(255),
  description VARCHAR(255),
  state TINYINT(1) COMMENT 'user state: 0=normal, 1=disable',
  deleted TINYINT(1) DEFAULT 0 COMMENT 'soft deleted: 0=undelete,1=deleted',
  update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY (id)
);

Role Permissions

CREATE TABLE IF NOT EXISTS role_permissions (
  id int(10) NOT NULL AUTO_INCREMENT,
  role_id INT(10),
  permission_id INT(10),
  state TINYINT(1) COMMENT 'user state: 0=normal, 1=disable',
  deleted TINYINT(1) DEFAULT 0 COMMENT 'soft deleted: 0=undelete,1=deleted',
  update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

Usergroups

CREATE TABLE IF NOT EXISTS usergroups (
  id int(10) NOT NULL AUTO_INCREMENT,
  parent_id INT(10) DEFAULT NULL,
  code VARCHAR(255) UNIQUE,
  name VARCHAR(255),
  description VARCHAR(255),
  state TINYINT(1) COMMENT 'user state: 0=normal, 1=disable',
  deleted TINYINT(1) DEFAULT 0 COMMENT 'soft deleted: 0=undelete,1=deleted',
  update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

Usergroup Users

CREATE TABLE IF NOT EXISTS usergroup_users (
  id int(10) NOT NULL AUTO_INCREMENT,
  usergroup_id INT(10),
  user_id INT(10),
  state TINYINT(1) COMMENT 'user state: 0=normal, 1=disable',
  deleted TINYINT(1) DEFAULT 0 COMMENT 'soft deleted: 0=undelete,1=deleted',
  update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

Role Usergroups

CREATE TABLE IF NOT EXISTS role_usergroups (
  id int(10) NOT NULL AUTO_INCREMENT,
  role_id INT(10),
  usergroup_id INT(10),
  state TINYINT(1) COMMENT 'user state: 0=normal, 1=disable',
  deleted TINYINT(1) DEFAULT 0 COMMENT 'soft deleted: 0=undelete,1=deleted',
  update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

SQL All in one

DROP database hfcms_users;
CREATE database hfcms_users DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
CREATE USER 'hfcms_users_user'@'%' IDENTIFIED BY 'hfcms_users_user_passwd';
GRANT ALL PRIVILEGES ON hfcms_users.* TO 'hfcms_users_user'@'%';
FLUSH PRIVILEGES;
USE hfcms_users;
CREATE TABLE IF NOT EXISTS users (
  id int(10) NOT NULL AUTO_INCREMENT,
  username VARCHAR(255) UNIQUE,
  password VARCHAR(255),
  realname VARCHAR(255),
  nickname VARCHAR(255),
  avatar_url VARCHAR(255),
  phone VARCHAR(11),
  user_ip INT(4) UNSIGNED,
  state TINYINT(1) DEFAULT 0 COMMENT 'user state: 0=normal, 1=disable',
  deleted TINYINT(1) DEFAULT 0 COMMENT 'soft deleted: 0=undelete,1=deleted',
  create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY (id)
);
CREATE TABLE IF NOT EXISTS departments (
  id int(10) NOT NULL AUTO_INCREMENT,
  parent_id INT(10) DEFAULT NULL,
  code VARCHAR(255) UNIQUE,
  name VARCHAR(255),
  description VARCHAR(255),
  state TINYINT(1) DEFAULT 0 COMMENT 'user state: 0=normal, 1=disable',
  deleted TINYINT(1) DEFAULT 0 COMMENT 'soft deleted: 0=undelete,1=deleted',
  update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS department_users (
  id int(10) NOT NULL AUTO_INCREMENT,
  department_id INT(10),
  user_id INT(10),
  state TINYINT(1) COMMENT 'user state: 0=normal, 1=disable',
  deleted TINYINT(1) DEFAULT 0 COMMENT 'soft deleted: 0=undelete,1=deleted',
  update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS roles (
  id int(10) NOT NULL AUTO_INCREMENT,
  parent_id INT(10) DEFAULT NULL,
  code VARCHAR(255) UNIQUE,
  name VARCHAR(255),
  description VARCHAR(255),
  state TINYINT(1) COMMENT 'user state: 0=normal, 1=disable',
  deleted TINYINT(1) DEFAULT 0 COMMENT 'soft deleted: 0=undelete,1=deleted',
  update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS role_users (
  id int(10) NOT NULL AUTO_INCREMENT,
  role_id INT(10),
  user_id INT(10),
  state TINYINT(1) COMMENT 'user state: 0=normal, 1=disable',
  deleted TINYINT(1) DEFAULT 0 COMMENT 'soft deleted: 0=undelete,1=deleted',
  update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS permissions (
  id int(10) NOT NULL AUTO_INCREMENT,
  parent_id INT(10) DEFAULT NULL,
  code VARCHAR(255) UNIQUE,
  name VARCHAR(255),
  description VARCHAR(255),
  state TINYINT(1) COMMENT 'user state: 0=normal, 1=disable',
  deleted TINYINT(1) DEFAULT 0 COMMENT 'soft deleted: 0=undelete,1=deleted',
  update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY (id)
);
CREATE TABLE IF NOT EXISTS role_permissions (
  id int(10) NOT NULL AUTO_INCREMENT,
  role_id INT(10),
  permission_id INT(10),
  state TINYINT(1) COMMENT 'user state: 0=normal, 1=disable',
  deleted TINYINT(1) DEFAULT 0 COMMENT 'soft deleted: 0=undelete,1=deleted',
  update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS usergroups (
  id int(10) NOT NULL AUTO_INCREMENT,
  parent_id INT(10) DEFAULT NULL,
  code VARCHAR(255) UNIQUE,
  name VARCHAR(255),
  description VARCHAR(255),
  state TINYINT(1) COMMENT 'user state: 0=normal, 1=disable',
  deleted TINYINT(1) DEFAULT 0 COMMENT 'soft deleted: 0=undelete,1=deleted',
  update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS usergroup_users (
  id int(10) NOT NULL AUTO_INCREMENT,
  usergroup_id INT(10),
  user_id INT(10),
  state TINYINT(1) COMMENT 'user state: 0=normal, 1=disable',
  deleted TINYINT(1) DEFAULT 0 COMMENT 'soft deleted: 0=undelete,1=deleted',
  update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS role_usergroups (
  id int(10) NOT NULL AUTO_INCREMENT,
  role_id INT(10),
  usergroup_id INT(10),
  state TINYINT(1) COMMENT 'user state: 0=normal, 1=disable',
  deleted TINYINT(1) DEFAULT 0 COMMENT 'soft deleted: 0=undelete,1=deleted',
  update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

Insert

INSERT INTO users(username, password, realname, nickname, avatar_url, phone, user_ip)
VALUES
  ('zhangsan', (SELECT PASSWORD('zhangsanpwd')), 'zhangsan', 'zhangsansan', 'zhangsan.jpg', '13912345678', INET_ATON('192.168.0.10')),
  ('lisi', (SELECT PASSWORD('lisipwd')), 'lisi', 'lisisi', 'lisi.jpg', '13912345679', INET_ATON('192.168.0.11')),
  ('wangwu', (SELECT PASSWORD('wangwupwd')), 'wangwu', 'wangwuwu', 'wangwu.jpg', '13912345670', INET_ATON('192.168.0.12')),
  ('test', (SELECT PASSWORD('testpwd')), 'test', 'testt', 'test.jpg', '13912345671', INET_ATON('192.168.1.13')),
  ('guest', (SELECT PASSWORD('guestpwd')), 'guest', 'guestt', 'guest.jpg', '13912345611', INET_ATON('192.168.1.14'));
INSERT INTO departments(parent_id, code, name, description)
VALUES
  (NULL, 'dbcd', 'SDP', 'Sales Department'),
  (NULL, 'dfgh', 'PRD', 'Public Relations Department'),
  (NULL, 'djkl', 'R&D', 'Research and Development Department'),
  (NULL, 'dnop', 'FD', 'Finance Department'),
  (NULL, 'drst', 'F&B', 'Food & Beverage Department'),
  (1, 'duvw', 'SDO', 'Sales Department Office');
INSERT INTO department_users(department_id, user_id)
VALUES
  (4, 3),
  (5, 4),
  (5, 2),
  (5, 1);
INSERT INTO roles(parent_id, code, name, description)
VALUES
  (NULL, 'rabc', 'master', 'You know, here\'s my spot.'),
  (NULL, 'rdef', 'submaster', 'You know, master is my boss.'),
  (NULL, 'rdeg', 'users', 'Ordinary people'),
  (NULL, 'rdeh', 'guest', 'May be newbie here'),
  (NULL, 'rghi', 'blocked', 'clear out here!');
INSERT INTO role_users(role_id, user_id)
VALUES
  (1, 1),
  (2, 2),
  (2, 3),
  (3, 4);
INSERT INTO permissions(parent_id, code, name, description)
VALUES
  (NULL, 'pabc', 'delete', 'oh no!'),
  (NULL, 'pdef', 'update', 'be carefull'),
  (NULL, 'pghi', 'create', 'Create what?'),
  (NULL, 'pjkl', 'query', 'ok, show you'),
  (2, 'pmon', 'article', 'update article');
INSERT INTO role_permissions(role_id, permission_id)
VALUES
  (1, 1),
  (1, 2),
  (1, 3),
  (1, 4),
  (1, 5),
  (2, 3),
  (2, 4),
  (3, 4);
INSERT INTO usergroups(parent_id, code, name, description)
VALUES
  (NULL, 'ug001', 'GameMaster', 'My plasure'),
  (NULL, 'ug100', 'Room Master', 'All rooms control'),
  (2, 'ug101', 'Room 1', 'good day'),
  (2, 'ug102', 'Room 2', 'good day'),
  (2, 'ug103', 'Room 3', 'good day');
INSERT INTO usergroup_users(usergroup_id, user_id)
VALUES
  (1, 1),
  (2, 2),
  (3, 3),
  (4, 3),
  (5, 3);
INSERT INTO role_usergroups(role_id, usergroup_id)
VALUES
  (1, 1),
  (2, 2),
  (3, 3),
  (3, 4),
  (3, 5);

Validate

select host,
       user as username,
       password,
       password_expired
from mysql.user
order by user;
SHOW GRANTS FOR hfcms_users_user;
describe users;
describe roles;
desc role_users;
desc permissions;
desc role_permissions;
desc usergroups;
desc usergroup_users;
desc role_usergroups;

Output:

MariaDB [hfcms_users]> select host,
    ->        user as username,
    ->        password,
    ->        password_expired
    -> from mysql.user
    -> order by user;
+-----------+---------------------+-------------------------------------------+------------------+
| Host      | username            | Password                                  | password_expired |
+-----------+---------------------+-------------------------------------------+------------------+
| %         | hfcms_articles_user | *7B5855D2183D391D34685174C1A975CD23979B17 | N                |
| %         | hfcms_users_user    | *CBF9C118375B4DBFCE57493A331215C4F059F702 | N                |
| localhost | mariadb.sys         |                                           | Y                |
| localhost | root                | *27C01464AD101AED1E65AC21152499A396B4CF72 | N                |
| %         | root                | *27C01464AD101AED1E65AC21152499A396B4CF72 | N                |
+-----------+---------------------+-------------------------------------------+------------------+
5 rows in set (0.001 sec)

MariaDB [hfcms_users]> SHOW GRANTS FOR hfcms_users_user;
+-----------------------------------------------------------------------------------------------------------------+
| Grants for hfcms_users_user@%                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `hfcms_users_user`@`%` IDENTIFIED BY PASSWORD '*CBF9C118375B4DBFCE57493A331215C4F059F702' |
| GRANT ALL PRIVILEGES ON `hfcms_users`.* TO `hfcms_users_user`@`%`                                               |
+-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

MariaDB [hfcms_users]> describe users;
+-------------+-----------------+------+-----+---------------------+-------------------------------+
| Field       | Type            | Null | Key | Default             | Extra                         |
+-------------+-----------------+------+-----+---------------------+-------------------------------+
| id          | int(10)         | NO   | PRI | NULL                | auto_increment                |
| username    | varchar(255)    | YES  |     | NULL                |                               |
| password    | varchar(255)    | YES  |     | NULL                |                               |
| realname    | varchar(255)    | YES  |     | NULL                |                               |
| nickname    | varchar(255)    | YES  |     | NULL                |                               |
| avatar_url  | varchar(255)    | YES  |     | NULL                |                               |
| phone       | varchar(11)     | YES  |     | NULL                |                               |
| user_ip     | int(4) unsigned | YES  |     | NULL                |                               |
| state       | tinyint(1)      | YES  |     | NULL                |                               |
| deleted     | tinyint(1)      | YES  |     | 0                   |                               |
| create_time | timestamp       | NO   |     | current_timestamp() |                               |
| update_time | timestamp       | NO   |     | current_timestamp() | on update current_timestamp() |
+-------------+-----------------+------+-----+---------------------+-------------------------------+
12 rows in set (0.001 sec)

MariaDB [hfcms_users]> describe roles;
+-------------+--------------+------+-----+---------------------+-------------------------------+
| Field       | Type         | Null | Key | Default             | Extra                         |
+-------------+--------------+------+-----+---------------------+-------------------------------+
| id          | int(10)      | NO   | PRI | NULL                | auto_increment                |
| parent_id   | int(10)      | YES  | UNI | NULL                |                               |
| code        | varchar(255) | YES  | UNI | NULL                |                               |
| name        | varchar(255) | YES  |     | NULL                |                               |
| description | varchar(255) | YES  |     | NULL                |                               |
| state       | tinyint(1)   | YES  |     | NULL                |                               |
| deleted     | tinyint(1)   | YES  |     | 0                   |                               |
| update_time | timestamp    | NO   |     | current_timestamp() | on update current_timestamp() |
+-------------+--------------+------+-----+---------------------+-------------------------------+
8 rows in set (0.001 sec)

MariaDB [hfcms_users]> desc role_users;
+-------------+------------+------+-----+---------------------+-------------------------------+
| Field       | Type       | Null | Key | Default             | Extra                         |
+-------------+------------+------+-----+---------------------+-------------------------------+
| id          | int(10)    | NO   | PRI | NULL                | auto_increment                |
| role_id     | int(10)    | YES  |     | NULL                |                               |
| user_id     | int(10)    | YES  |     | NULL                |                               |
| state       | tinyint(1) | YES  |     | NULL                |                               |
| deleted     | tinyint(1) | YES  |     | 0                   |                               |
| update_time | timestamp  | NO   |     | current_timestamp() | on update current_timestamp() |
+-------------+------------+------+-----+---------------------+-------------------------------+
6 rows in set (0.001 sec)

MariaDB [hfcms_users]> desc permissions;
+-------------+--------------+------+-----+---------------------+-------------------------------+
| Field       | Type         | Null | Key | Default             | Extra                         |
+-------------+--------------+------+-----+---------------------+-------------------------------+
| id          | int(10)      | NO   | PRI | NULL                | auto_increment                |
| parent_id   | int(10)      | YES  | UNI | NULL                |                               |
| code        | varchar(255) | YES  | UNI | NULL                |                               |
| name        | varchar(255) | YES  |     | NULL                |                               |
| description | varchar(255) | YES  |     | NULL                |                               |
| state       | tinyint(1)   | YES  |     | NULL                |                               |
| deleted     | tinyint(1)   | YES  |     | 0                   |                               |
| update_time | timestamp    | NO   |     | current_timestamp() | on update current_timestamp() |
+-------------+--------------+------+-----+---------------------+-------------------------------+
8 rows in set (0.001 sec)

MariaDB [hfcms_users]> desc role_permissions;
+---------------+------------+------+-----+---------------------+-------------------------------+
| Field         | Type       | Null | Key | Default             | Extra                         |
+---------------+------------+------+-----+---------------------+-------------------------------+
| id            | int(10)    | NO   | PRI | NULL                | auto_increment                |
| role_id       | int(10)    | YES  |     | NULL                |                               |
| permission_id | int(10)    | YES  |     | NULL                |                               |
| state         | tinyint(1) | YES  |     | NULL                |                               |
| deleted       | tinyint(1) | YES  |     | 0                   |                               |
| update_time   | timestamp  | NO   |     | current_timestamp() | on update current_timestamp() |
+---------------+------------+------+-----+---------------------+-------------------------------+
6 rows in set (0.001 sec)

MariaDB [hfcms_users]> desc usergroups;
+-------------+--------------+------+-----+---------------------+-------------------------------+
| Field       | Type         | Null | Key | Default             | Extra                         |
+-------------+--------------+------+-----+---------------------+-------------------------------+
| id          | int(10)      | NO   | PRI | NULL                | auto_increment                |
| parent_id   | int(10)      | YES  | UNI | NULL                |                               |
| code        | varchar(255) | YES  | UNI | NULL                |                               |
| name        | varchar(255) | YES  |     | NULL                |                               |
| description | varchar(255) | YES  |     | NULL                |                               |
| state       | tinyint(1)   | YES  |     | NULL                |                               |
| deleted     | tinyint(1)   | YES  |     | 0                   |                               |
| update_time | timestamp    | NO   |     | current_timestamp() | on update current_timestamp() |
+-------------+--------------+------+-----+---------------------+-------------------------------+
8 rows in set (0.001 sec)

MariaDB [hfcms_users]> desc usergroup_users;
+--------------+------------+------+-----+---------------------+-------------------------------+
| Field        | Type       | Null | Key | Default             | Extra                         |
+--------------+------------+------+-----+---------------------+-------------------------------+
| id           | int(10)    | NO   | PRI | NULL                | auto_increment                |
| usergroup_id | int(10)    | YES  |     | NULL                |                               |
| user_id      | int(10)    | YES  |     | NULL                |                               |
| state        | tinyint(1) | YES  |     | NULL                |                               |
| deleted      | tinyint(1) | YES  |     | 0                   |                               |
| update_time  | timestamp  | NO   |     | current_timestamp() | on update current_timestamp() |
+--------------+------------+------+-----+---------------------+-------------------------------+
6 rows in set (0.001 sec)

MariaDB [hfcms_users]> desc role_usergroups;
+--------------+------------+------+-----+---------------------+-------------------------------+
| Field        | Type       | Null | Key | Default             | Extra                         |
+--------------+------------+------+-----+---------------------+-------------------------------+
| id           | int(10)    | NO   | PRI | NULL                | auto_increment                |
| role_id      | int(10)    | YES  |     | NULL                |                               |
| usergroup_id | int(10)    | YES  |     | NULL                |                               |
| state        | tinyint(1) | YES  |     | NULL                |                               |
| deleted      | tinyint(1) | YES  |     | 0                   |                               |
| update_time  | timestamp  | NO   |     | current_timestamp() | on update current_timestamp() |
+--------------+------------+------+-----+---------------------+-------------------------------+
6 rows in set (0.001 sec)

Documentation

Index

Constants

This section is empty.

Variables

View Source 
var (
	ErrNotFound   = errors.New("Item not found in table")
	ErrValidation = errors.New("Valid column error")
)

Functions

This section is empty.

Types

type Client 

type Client struct {
	DatabaseClient *DatabaseClient
	// contains filtered or unexported fields
}

func NewClient 

func NewClient(projectName configs.ProjectName) (*Client, error)

type DatabaseClient 

type DatabaseClient struct {
	// contains filtered or unexported fields
}

func (*DatabaseClient) DeleteDepartment 

func (dc *DatabaseClient) DeleteDepartment(ctx context.Context, id int) error

DeleteDepartment is soft delete, not delete from database, but update deleted field to 1 DeleteDepartment is cooperate with All(ctx), that just return all rows except deleted is 1

func (*DatabaseClient) DeleteDepartment2 

func (dc *DatabaseClient) DeleteDepartment2(ctx context.Context, id int) error

DeleteDepartment2 is true delete from database instead of DeleteDepartment just update the row

func (*DatabaseClient) DeleteRole 

func (dc *DatabaseClient) DeleteRole(ctx context.Context, id int) error

DeleteRole is soft delete, not delete from database, but update deleted field to 1 DeleteRole is cooperate with All(ctx), that just return all rows except deleted is 1

func (*DatabaseClient) DeleteRole2 

func (dc *DatabaseClient) DeleteRole2(ctx context.Context, id int) error

DeleteRole2 is true delete from database instead of DeleteRole just update the row

func (*DatabaseClient) DeleteUser 

func (dc *DatabaseClient) DeleteUser(ctx context.Context, id int) error

DeleteUser is soft delete, not delete from database, but update deleted field to 1 DeleteUser is cooperate with All(ctx), that just return all rows except deleted is 1

func (*DatabaseClient) DeleteUser2 

func (dc *DatabaseClient) DeleteUser2(ctx context.Context, id int) error

DeleteUser2 is true delete from database instead of DeleteUser just update the row

func (*DatabaseClient) InsertDepartment 

func (dc *DatabaseClient) InsertDepartment(ctx context.Context, department *Department) error

func (*DatabaseClient) InsertRole 

func (dc *DatabaseClient) InsertRole(ctx context.Context, role *Role) error

func (*DatabaseClient) InsertUser 

func (dc *DatabaseClient) InsertUser(ctx context.Context, user *User) error

func (*DatabaseClient) QueryDepartment 

func (dc *DatabaseClient) QueryDepartment() *DepartmentQuery

func (*DatabaseClient) QueryRole 

func (dc *DatabaseClient) QueryRole() *RoleQuery

func (*DatabaseClient) QueryUser 

func (dc *DatabaseClient) QueryUser() *UserQuery

func (*DatabaseClient) UndeleteDepartment 

func (dc *DatabaseClient) UndeleteDepartment(ctx context.Context, id int) error

func (*DatabaseClient) UndeleteRole 

func (dc *DatabaseClient) UndeleteRole(ctx context.Context, id int) error

func (*DatabaseClient) UndeleteUser 

func (dc *DatabaseClient) UndeleteUser(ctx context.Context, id int) error

func (*DatabaseClient) UpdateDepartment 

func (dc *DatabaseClient) UpdateDepartment(ctx context.Context, department *Department) error

func (*DatabaseClient) UpdateRole 

func (dc *DatabaseClient) UpdateRole(ctx context.Context, role *Role) error

func (*DatabaseClient) UpdateUser 

func (dc *DatabaseClient) UpdateUser(ctx context.Context, user *User) error

type Department 

type Department struct {
	DepartmentId, ParentId, State, Deleted      int
	DepartmentCode, DepartmentName, Description string
	UpdateTime                                  time.Time
}

type DepartmentQuery 

type DepartmentQuery struct {
	// contains filtered or unexported fields
}

func (*DepartmentQuery) All 

func (aq *DepartmentQuery) All(ctx context.Context) (*Departments, error)

All will display all lines that the deleted field value is 0

func (*DepartmentQuery) All2 

func (aq *DepartmentQuery) All2(ctx context.Context) (*Departments, error)

All2 will display all rows even if deleted field value is 1

func (*DepartmentQuery) First 

func (aq *DepartmentQuery) First(ctx context.Context) (*Department, error)

func (*DepartmentQuery) Limit 

func (aq *DepartmentQuery) Limit(limit int) *DepartmentQuery

func (*DepartmentQuery) Offset 

func (aq *DepartmentQuery) Offset(offset int) *DepartmentQuery

func (*DepartmentQuery) Order 

func (aq *DepartmentQuery) Order(condition string) *DepartmentQuery

func (*DepartmentQuery) Where 

func (aq *DepartmentQuery) Where(cs ...[4]string) *DepartmentQuery

cs: {["name", "=", "jack", "and"], ["title", "like", "anything", ""]} the last `or` or `and` in clause will cut off after prepareQuery(). so, every clause need `or` or `and` for last element.

type Departments 

type Departments struct {
	Collection []*Department
}

type Role 

type Role struct {
	RoleId, ParentId, State, Deleted int
	RoleCode, RoleName, Description  string
	UpdateTime                       time.Time
}

type RoleQuery 

type RoleQuery struct {
	// contains filtered or unexported fields
}

func (*RoleQuery) All 

func (aq *RoleQuery) All(ctx context.Context) (*Roles, error)

All will display all lines that the deleted field value is 0

func (*RoleQuery) All2 

func (aq *RoleQuery) All2(ctx context.Context) (*Roles, error)

All2 will display all rows even if deleted field value is 1

func (*RoleQuery) First 

func (aq *RoleQuery) First(ctx context.Context) (*Role, error)

func (*RoleQuery) Limit 

func (aq *RoleQuery) Limit(limit int) *RoleQuery

func (*RoleQuery) Offset 

func (aq *RoleQuery) Offset(offset int) *RoleQuery

func (*RoleQuery) Order 

func (aq *RoleQuery) Order(condition string) *RoleQuery

func (*RoleQuery) Where 

func (aq *RoleQuery) Where(cs ...[4]string) *RoleQuery

cs: {["name", "=", "jack", "and"], ["title", "like", "anything", ""]} the last `or` or `and` in clause will cut off after prepareQuery(). so, every clause need `or` or `and` for last element.

type Roles 

type Roles struct {
	Collection []*Role
}

type User 

type User struct {
	Id, State, Deleted                                               int
	Username, Password, Realname, Nickname, AvatarUrl, Phone, UserIP string
	CreateTime, UpdateTime                                           time.Time
}

type UserQuery 

type UserQuery struct {
	// contains filtered or unexported fields
}

func (*UserQuery) All 

func (aq *UserQuery) All(ctx context.Context) (*Users, error)

All will display all lines that the deleted field value is 0

func (*UserQuery) All2 

func (aq *UserQuery) All2(ctx context.Context) (*Users, error)

All2 will display all rows even if deleted field value is 1

func (*UserQuery) First 

func (aq *UserQuery) First(ctx context.Context) (*User, error)

func (*UserQuery) Limit 

func (aq *UserQuery) Limit(limit int) *UserQuery

func (*UserQuery) Offset 

func (aq *UserQuery) Offset(offset int) *UserQuery

func (*UserQuery) Order 

func (aq *UserQuery) Order(condition string) *UserQuery

func (*UserQuery) Where 

func (aq *UserQuery) Where(cs ...[4]string) *UserQuery

cs: {["name", "=", "jack", "and"], ["title", "like", "anything", ""]} the last `or` or `and` in clause will cut off after prepareQuery(). so, every clause need `or` or `and` for last element.

type Users 

type Users struct {
	Collection []*User
}

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.