SaaS solutions implemented through tenant ids

General situation of

Halfway through the project, the user suddenly proposed the need for multiple branches to share the system, which needed to design the system as a SaaS architecture, isolating the data of each branch.

SaaS implementation solutions

Standalone database

Each enterprise independent physical database, good isolation, high cost.
Shared database, independent schema

It is a physical machine, multiple logical databases, Oracle called schema, mysql called database, each enterprise independent schema.
Sharing database and database table (adopted this time) :

Add enterprise or Tenant fields to the table to distinguish the enterprise data. Query the corresponding data according to the “Tenant” field.

Advantages: All tenants use the same database, so the cost is low.

Disadvantages: Low isolation level, low security, need to increase the amount of security development during development, data backup and recovery is the most difficult.

Transformation ideas

The useShare databases and database tablesSaaS solutions. The following work needs to be done during the transformation:

Create a tenant information table.
Start by adding a tenant ID field to all tablestenant_id. Used to associate tenant information tables.
willtenant_idCreate a joint primary key with the original table ID. Note the order of the primary keys, the original table primary keys must be on the left.
Change the table to a partitioned table.

After the transformation, a partition for the tenant is added to all tables when the tenant information is added. The partition is used to store the tenant data.
When records are added later, thetenant_idThe value of a field must be specified in the where conditiontenant_idTo manipulate a tenant’s data for conditions.

Test Environment introduction

There are five tables in the test library, which I will use the SYS_log table for testing below.

sys_log

CREATE TABLE `sys_log` (
  `log_id` BIGINT NOT NULL AUTO_INCREMENT COMMENT 'primary key'.`type` TINYINT(1) DEFAULT NULL COMMENT 'type'.`content` VARCHAR(255) DEFAULT NULL COMMENT 'content'.`create_id` BIGINT(18) DEFAULT NULL COMMENT 'creator ID'.`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time'.`tenant_id` INT NOT NULL,
  PRIMARY KEY (`log_id`.`tenant_id`) USING BTREE
) ENGINE=INNODB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='System Log'
Copy the code

Add the tenant ID field to the table

Find not added`Tenant ID (tenant_id)`In the table.

SELECT 
    table_name 
  FROM
    INFORMATION_SCHEMA.TABLES
  WHERE table_schema = 'my'   -- my is my test database name
    AND table_name NOT IN 
    (SELECT 
      t.table_name 
    FROM
      (SELECT 
        table_name,
        column_name 
      FROM
        information_schema.columns 
      WHERE table_name IN 
        (SELECT 
          table_name 
        FROM
          INFORMATION_SCHEMA.TABLES 
        WHERE table_schema = 'my')) t 
    WHERE t.column_name = 'tenant_id');Copy the code

Select * from tenant_ID where tenant_ID = tenant_ID;

Create a tenant information table

Save tenant information for reference only

CREATE TABLE `t_tenant` (
  `tenant_id` varchar(40) NOT NULL DEFAULT 'c12dee54f652452b88142a0267ec74b7' COMMENT 'the tenant id'.`tenant_code` varchar(100) DEFAULT NULL COMMENT 'Tenant code'.`name` varchar(50) DEFAULT NULL COMMENT 'Tenant Name'.`desc` varchar(500) DEFAULT NULL COMMENT 'Tenant Description'.`logo` varchar(255) DEFAULT NULL COMMENT 'Company logo Address'.`status` smallint(6) DEFAULT NULL COMMENT 'State 1 valid 0 invalid'.`create_by` varchar(100) DEFAULT NULL COMMENT 'Creator'.`create_time` datetime DEFAULT NULL COMMENT 'Creation time'.`last_update_by` varchar(100) DEFAULT NULL COMMENT 'Last Modifier'.`last_update_time` datetime DEFAULT NULL COMMENT 'Last modified time'.`street_address` varchar(200) DEFAULT NULL COMMENT 'Street Building Address'.`province` varchar(20) DEFAULT NULL COMMENT 'First-class administrative units, such as Guangdong Province, Shanghai Municipality'.`city` varchar(20) DEFAULT NULL COMMENT 'Cities such as Guangzhou, Foshan, etc.'.`district` varchar(20) DEFAULT NULL COMMENT 'Administrative districts, such as Panyu, Tianhe, etc.'.`link_man` varchar(50) DEFAULT NULL COMMENT 'Contacts'.`link_phone` varchar(50) DEFAULT NULL COMMENT 'Contact Number'.`longitude` decimal(10.6) DEFAULT NULL COMMENT 'longitude'.`latitude` decimal(10.6) DEFAULT NULL COMMENT 'latitude'.`adcode` varchar(8) DEFAULT NULL COMMENT 'Region code, used for quick matching by region ID after display, e.g. Guangzhou is 440100',
  PRIMARY KEY (`tenant_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Tenant Basic Information Table';
Copy the code

Add all tables`tenant_id`field

DROP PROCEDURE IF EXISTS addColumn ; DELIMITER ? CREATE PROCEDURE addColumn () BEGIN -- Define the tablename variable DECLARE S_TABlename VARCHAR (100); /* SELECT table_name FROM information_schema.tables WHERE table_schema='databasename' Order by table_name; */ # display all DECLARE cur_table_structure CURSOR FOR SELECT table_name FROM information_schema. TABLES WHERE table_schema = SELECT table_name FROM (SELECT table_name FROM (SELECT table_name FROM (SELECT table_name, column_name FROM information_schema.columns WHERE table_name IN (SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'my')) t WHERE t.column_name = 'tenant_id') ; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s_tablename = NULL ; OPEN cur_table_structure ; FETCH cur_table_structure INTO s_tablename ; WHILE (s_tablename IS NOT NULL) DO SET @MyQuery = CONCAT( "alter table `", s_tablename, "' add COLUMN 'tenant_id' INT not null COMMENT 'vstore id'"); PREPARE msql FROM @MyQuery ; EXECUTE msql ; #USING @c; FETCH cur_table_structure INTO s_tablename ; END WHILE ; CLOSE cur_table_structure ; END ? DELIMITER ; CALL addColumn ();Copy the code

Implementing table partitioning

Goal achieved: Partitioning all tables when tenants are added

Requirements:

The table must be a partitioned table. If it is not a partitioned table, change it to a partitioned table.
tenant_idMust be identical to the original tablelog_idThe primary key forms the combined primary key.

Modify the table to the component area table

There are three ways to add a partition to a table:

Create a temporary partition tablesys_log_copyCopy the data and delete the old onesys_logAnd thensys_log_copyModified tosys_log(This time, see below)
Mysql > alter table table_name; alter table table_name; alter table table_name;

If there is no data in the table, partition the table directly
ALTER TABLE sys_log PARTITION BY LIST COLUMNS (tenant_id)
(
    PARTITION a1 VALUES IN (1) ENGINE = INNODB.PARTITION a2 VALUES IN (2) ENGINE = INNODB.PARTITION a3 VALUES IN (3) ENGINE = INNODB
);
Copy the code

Add a new partition to a partitioned table. Add a new partition to a partitioned table.

Add a partition to the partition table
ALTER TABLE sys_log_copy ADD PARTITION
(
    PARTITION a4 VALUES IN (4) ENGINE = INNODB.PARTITION a5 VALUES IN (5) ENGINE = INNODB.PARTITION a6 VALUES IN (6) ENGINE = INNODB
);
Copy the code

through`Create a temporary partition table`Convert the original table to the component area table

Table construction sentences:

SHOW CREATE TABLE `sys_log`;
Copy the code

Create a copy table as follows:

CREATE TABLE `sys_log_copy` (
  `log_id` BIGINT NOT NULL AUTO_INCREMENT COMMENT 'primary key'.`type` TINYINT(1) DEFAULT NULL COMMENT 'type'.`content` VARCHAR(255) DEFAULT NULL COMMENT 'content'.`create_id` BIGINT(18) DEFAULT NULL COMMENT 'creator ID'.`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time'.`tenant_id` INT NOT NULL,
  PRIMARY KEY (`log_id`.`tenant_id`) USING BTREE
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='System Log'
PARTITION BY LIST COLUMNS (tenant_id)
(
    PARTITION a1 VALUES IN (1) ENGINE = INNODB.PARTITION a2 VALUES IN (2) ENGINE = INNODB.PARTITION a3 VALUES IN (3) ENGINE = INNODB
);
Copy the code

Note that the DEFAULT CHARSET= UTf8MB4 ROW_FORMAT=DYNAMIC

CHARSET=utf8mb4Utf8 is an unsound encoding in mysql.

ROW_FORMAT=DYNAMICIn order to avoid the following error if the length is too large:
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.
Copy the code
You can also fix this by setting it to true in the my.ini configuration file, but restarting the database will be more troublesome.
[mysqld]
innodb_large_prefix=true
Copy the code

Verify partitioning:

SELECT 
  partition_name part,
  partition_expression expr,
  partition_description descr,
  table_rows 
FROM
  information_schema.partitions 
WHERE TABLE_SCHEMA = SCHEMA(a)AND TABLE_NAME = 'sys_log_copy' ;
Copy the code

You can view the three partitions that have been added

Copy the data to the copy table

INSERT INTO `sys_log_copy` SELECT * FROM `sys_log`
Copy the code

Delete tablesys_logThe revision,sys_log_copyThe names in the table aresys_log.

Write warehouse procedures that automatically create partitions

A partition is added to a partitioned table through a stored procedure

DELIMITER ? USE `my`? DROP PROCEDURE IF EXISTS `add_table_partition`? CREATE DEFINER=`root`@`%` PROCEDURE `add_table_partition`(IN _tenantId INT) BEGIN DECLARE IS_FOUND INT DEFAULT 1 ; -- DECLARE v_TABlename VARCHAR (200); -- DECLARE v_tablename VARCHAR (200); -- SQL DECLARE v_SQL VARCHAR (5000) for cache add partition; -- Partition name define DECLARE V_P_VALUE VARCHAR (100) DEFAULT CONCAT('P', REPLACE(_tenantId, '-', '')); DECLARE V_COUNT INT ; DECLARE V_LOONUM INT DEFAULT 0 ; DECLARE V_NUM INT DEFAULT 0 ; Define cursor, DECLARE curr CURSOR FOR (SELECT T. table_name FROM INFORMATION_SCHEMA. Partitions T WHERE TABLE_SCHEMA = SCHEMA() AND t.partition_name IS NOT NULL GROUP BY t.TABLE_NAME) ; DECLARE CONTINUE HANDLER FOR NOT FOUND SET IS_FOUND=0; DECLARE CONTINUE HANDLER FOR NOT FOUND SET IS_FOUND=0; SELECT COUNT(1) INTO V_LOONUM FROM (SELECT t. table_name FROM information_schema. partitions t WHERE TABLE_SCHEMA = SCHEMA() AND t.partition_name IS NOT NULL GROUP BY t.TABLE_NAME) A ; IF V_LOONUM > 0 THEN -- OPEN cursor curr; -- LOOP read_loop: LOOP -- IF V_NUM >= V_LOONUM THEN LEAVE read_loop; END IF ; FETCH curr INTO v_tablename; V_NUM = V_NUM + 1; V_NUM = V_NUM + 1; SELECT COUNT(1) INTO V_COUNT FROM INFORMATION_SCHEMA.partitions t WHERE LOWER(T.TABLE_NAME) = LOWER(v_tablename) AND T.PARTITION_NAME = V_P_VALUE AND T.TABLE_SCHEMA = SCHEMA() ; IF V_COUNT <= 0 THEN SET v_sql = CONCAT( ' ALTER TABLE ', v_tablename, ' ADD PARTITION (PARTITION ', V_P_VALUE, ' VALUES IN(', _tenantId, ') ENGINE = INNODB) ' ) ; SET @v_sql = v_sql ; PREPARE STMT FROM @v_sql; SQL statement EXECUTE STMT; -- DEALLOCATE prepares STMT; END IF ; -- END LOOP read_loop; CLOSE curr; END IF ; END? DELIMITER ;Copy the code

Invoke stored procedure tests

CALL add_table_partition (8) ;
Copy the code

If the table is not yet a partitioned table, the stored procedure will be called with the following error:
1505 Partition management on a not Partitioned table is not possibleCopy the code
Partition management is not possible on an unpartitioned table.

An error may occur as follows:
Error code: 1329 No data-zero rows fetched, selected, or processedCopy the code
However, if query for Information_schema. partitions below is correct, the partition was added successfully.

You can solve this problem by adding the following to the cursor definition and before the cursor is opened:
DECLARE CONTINUE HANDLER FOR NOT FOUND SET IS_FOUND=0;
Copy the code

SELECT 
  partition_name part,
  partition_expression expr,
  partition_description descr,
  table_rows 
FROM
  information_schema.partitions 
WHERE TABLE_SCHEMA = SCHEMA(a)AND TABLE_NAME = 'sys_log' ;
Copy the code

through`mybatis`Calling a stored procedure

<select id="testProcedure" statementType="CALLABLE" useCache="false" parameterType="string"> <! [CDATA[ call add_table_partition ( #{_tenantId,mode=IN,jdbcType=VARCHAR}); ]]> </select>Copy the code

Implement simple data permissions

We may need this scenario requirement

The group company has several subsidiaries. The group company and each subsidiary is a tenant respectively, but there are also subsidiaries under the subsidiaries.
Both group companies and subsidiaries have corresponding users (T_user).
Users must have the permission to view the data of their own companies and subsidiaries.

From the scenario requirements above, we know that the T_tenant table needs to be designed as a tree stump structure. Let’s test it out.

Modify the above`t_tenant`Table as follows:

CREATE TABLE `t_tenant` (
  `tenant_id` VARCHAR(40) NOT NULL DEFAULT '0' COMMENT 'the tenant id'.`path` VARCHAR(200) DEFAULT NOT NULL COMMENT 'Path tree of ids from the root node, for example, 0-2-21-211-2111, separated by "-", end with its own ID'..`tenant_code` VARCHAR(100) DEFAULT NULL COMMENT 'Tenant code'.`name` VARCHAR(50) DEFAULT NULL COMMENT 'Tenant Name'.`logo` VARCHAR(255) DEFAULT NULL COMMENT 'Company logo Address'.`status` SMALLINT(6) DEFAULT NULL COMMENT 'State 1 valid 0 invalid'.`create_by` VARCHAR(100) DEFAULT NULL COMMENT 'Creator'.`create_time` DATETIME DEFAULT NULL COMMENT 'Creation time'.`last_update_by` VARCHAR(100) DEFAULT NULL COMMENT 'Last Modifier'.`last_update_time` DATETIME DEFAULT NULL COMMENT 'Last modified time'.`street_address` VARCHAR(200) DEFAULT NULL COMMENT 'Street Building Address',
  PRIMARY KEY (`tenant_id`) USING BTREE
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='Tenant Basic Information Table'
Copy the code

The changes are:

For demonstration purposes, I removed some fields that felt useless
addedpathField to realize the tree structure of tenants and sub-tenants

Adding test Data

Add tenant information:

The path of the T_tenant tree is cached through path.

Create user table (t_user), add test user:

The user ID and tenant_id must correspond

Create attachment table (T_file), add test service data:

The create field (create_by) associates the user table (T_user) with the tenant (tenant_id), specifying which subsidiary is the data.

test

View information about the tenant whose tenant_id is “211” and its subtenants

SELECT 
  tt.`tenant_id`,
  tt.path 
FROM
  t_tenant tt 
WHERE 
  (SELECT 
    INSTR(tt.path, "211"));Copy the code

View the attachment information for tenant_id is “211” and the attachment information for the subtenant under it

SELECT 
  * 
FROM
  t_file tf 
WHERE tf.`tenant_id` IN 
  (SELECT 
    tt.`tenant_id` 
  FROM
    t_tenant tt 
  WHERE 
    (SELECT 
      INSTR(tt.path, "211")));Copy the code

View attachment information about the subtenant whose tenant_id is “2”

The myBatis interceptor allows you to view the data of subtenants

Writing interceptors:

package com.iee.orm.mybatis.common;

import com.baomidou.mybatisplus.core.toolkit.PluginUtils;
import com.iee.orm.mybatis.common.UserHelper;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.mapping.StatementType;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.springframework.context.annotation.Configuration;

import java.sql.Connection;
import java.util.Properties;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/** * implement interception select statement, implement tail concatenation SQL to query the local tenant and sub-tenant information *@author [email protected]
 */
@Slf4j
@Configuration
@Intercepts({
        @Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})
})
public class SqlInterceptor implements Interceptor {
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        StatementHandler statementHandler = PluginUtils.realTarget(invocation.getTarget());
        MetaObject metaObject = SystemMetaObject.forObject(statementHandler);
        // Skip non-SELECT statements or stored procedures)
        MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedstatement");
        if(SqlCommandType.SELECT ! = mappedStatement.getSqlCommandType() || StatementType.CALLABLE == mappedStatement.getStatementType()) {return invocation.proceed();
        }
        // Splice SQL execution
        getSqlByInvocation(metaObject, invocation);
        return invocation.proceed();
    }

    /** * concatenate SQL execution *@param metaObject
     * @param invocation
     * @return* /
    private String getSqlByInvocation(MetaObject metaObject, Invocation invocation) throws NoSuchFieldException, IllegalAccessException {
        // Assemble the SQL from the original SQL, method 1
        String originalSql = (String) metaObject.getValue(PluginUtils.DELEGATE_BOUNDSQL_SQL);
        metaObject.setValue(PluginUtils.DELEGATE_BOUNDSQL_SQL, originalSql);
        String targetSql = addDataSql(originalSql);
        return targetSql;

        // Assemble the SQL from the original SQL
// StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
// BoundSql boundSql = statementHandler.getBoundSql();
// String sql = boundSql.getSql();
// Field field = boundSql.getClass().getDeclaredField("sql");
// field.setAccessible(true);
// field.set(boundSql, addDataSql(sql));
// return sql;
    }

    /** * concatenate the original SQL@param sql
     * @return* /
    static String addDataSql(String sql) {
        // Need to drop ";" Because the ";" End of SQL, if not removed, subsequent concatenation will be affected
        sql = StringUtils.replace(sql, ";"."");
        StringBuilder sb = new StringBuilder(sql);
        String tenantId = UserHelper.getTenantId();

        // The SQL suffix used to view sub-tenant information
        String suffSql = " `tenant_id` IN " +
                "(SELECT " +
                "tt.`tenant_id` " +
                "FROM " +
                "t_tenant tt " +
                "WHERE " +
                "(SELECT " +
                "INSTR(tt.path," + tenantId + ")))";

        String regex = "(.*)(where)(.*)";
        Pattern compile = Pattern.compile(regex);
        Matcher matcher = compile.matcher(sql);
        if (matcher.find()) {
            String whereLastSql = matcher.group(matcher.groupCount());
            // If the condition where exists and the parentheses are correct, you cannot add "where", but you need to add "and".
            int left = StringUtils.countMatches(whereLastSql, "(");
            int right = StringUtils.countMatches(whereLastSql, ")");
            if(left == right){
                sb.append(" and ");
                sb.append(suffSql);
                log.info(SQL > alter database name; + sb.toString());
                returnsb.toString(); }}// Add where in other cases
        sb.append(" where ");
        sb.append(suffSql);
        log.info(SQL > alter database name; + sb.toString());
        return sb.toString();
    }

    @Override
    public Object plugin(Object target) {
        return Plugin.wrap(target, new SqlInterceptor());
    }

    @Override
    public void setProperties(Properties properties) {}}Copy the code

Mybatis – Plus utility class is used

/* * Copyright (c) 2011-2020, baomidou ([email protected]). * <p> * 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 * <p> * https://www.apache.org/licenses/LICENSE-2.0 * < p > * 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. */ package com.baomidou.mybatisplus.core.toolkit; import org.apache.ibatis.reflection.MetaObject; import org.apache.ibatis.reflection.SystemMetaObject; import java.lang.reflect.Proxy; import java.util.Properties; ** * @author TaoYu, hubin * @since 2017-06-20 */ public final class PluginUtils { public static final String DELEGATE_BOUNDSQL_SQL = "delegate.boundSql.sql"; Private PluginUtils() {// to do nothing} /** * get real handlers, possibly multi-layer proxies. */ @suppressWarnings ("unchecked") public static <T> T  realTarget(Object target) { if (Proxy.isProxyClass(target.getClass())) { MetaObject metaObject = SystemMetaObject.forObject(target); return realTarget(metaObject.getValue("h.target")); } return (T) target; } /** * public static String getProperty(Properties); String key) { String value = properties.getProperty(key); return StringUtils.isEmpty(value) ? null : value; }}Copy the code

During the test, it was found that as long as the SELECT statement was associated with the query of sub-tenant information.

See the test code:

Github.com/longxiaonan…

Your encouragement of my motivation, please support, thank you!

SaaS solutions implemented through tenant ids

General situation of

SaaS implementation solutions

Transformation ideas

Test Environment introduction

Add the tenant ID field to the table

Find not addedTenant ID (tenant_id)In the table.

Create a tenant information table

Add all tablestenant_idfield

Implementing table partitioning

Modify the table to the component area table

throughCreate a temporary partition tableConvert the original table to the component area table

Write warehouse procedures that automatically create partitions

throughmybatisCalling a stored procedure

Implement simple data permissions

Modify the abovet_tenantTable as follows:

Adding test Data

test

The myBatis interceptor allows you to view the data of subtenants

Related Posts

Periodic MySQL backup script in Windows

Oracle pfile parameter file

Series TCP/IP protocol -UDP (009)

Find not added`Tenant ID (tenant_id)`In the table.

Add all tables`tenant_id`field

through`Create a temporary partition table`Convert the original table to the component area table

through`mybatis`Calling a stored procedure

Modify the above`t_tenant`Table as follows: