API数据源接入指南(下方示例程序基于PHP框架Yii2.0)

接入SDK

目前仅支持mysql数据源接入,待后续实现不同数据源的接入(oracle、mssql...);由于API数据源的数据并不保存或同步到DMP平台数据系统中,因此API数据集不能用于组合数据集;

1. 应用场景

API数据源,DMP平台不保存或同步数据到DMP平台数据系统中,因此接入端有更多权限去处理数据,如索引建立、数据更新同步等。同时具体查询DMP平台只是调用接入方的API,DMP系统传递查询相关的结构,如何生成查询语句由接入方决定(DMP平台提供基于PHP Yii2框架的基础SDK),因此接入方可以加入自己的业务逻辑,如权限管理、数据过滤等。综上API数据源是十分灵活的实现方式,如果接入方要保证DMP系统查询的数据数据和接入方业务系统的数据实时一致或者接入方有自己的业务逻辑需要加入,请使用API数据源。但是接入API数据源对接入方面技术能力有一定的挑战,如需要编写程序接入到自己的业务系统,需要保证数据查询效率,需要保证数据系统稳定,因此不具备此能力的项目团队,建议使用DMP平台的其他数据源(如MySql数据源、Excel数据源等)

2. 安装SDK

composer require mysoft/dmp

3. 关键配置

配置DMP_SECRET & 注入自定义数据源服务

config.php 文件配置如下

<?php
putenv('DMP_SECRET=1234567890123456');
\dmp\di\Container::getInstance()->set('dmp\extend\datasource\base\interfaces\DataSourceInterface', 'DataSourceService');

4. 继承数据源标准Controller

SDK中的Controller目前是基于Yii 2.0 实现。 若与现有使用框架不匹配,则自己实现dmp\extend\datasource\base\interfaces\DataSourceControllerInterface即可

dmp\extend\datasource\mysql\controllers\MysqlDataSourceController\DataSourceController.php 示例代码如下

<?php
use dmp\extend\datasource\mysql\controllers\MysqlDataSourceController;
class DataSourceController extends MysqlDataSourceController
{

}

实现数据源服务

  1. 实现数据源服务的关键代码如下 dmp\extend\datasource\mysql\services\DataSourceService.php
<?php

use dmp\extend\datasource\base\models\data\Condition;
use dmp\extend\datasource\base\models\data\DataQueryBuilder;
use dmp\extend\datasource\base\models\data\Property;
use dmp\extend\datasource\base\models\QueryBuilderBase;
use dmp\extend\datasource\base\models\struct\BizParameter;
use dmp\extend\datasource\mysql\services\MySQLDataSourceService;
use dmp\helper\db\mysql\Connection;

class DataSourceService extends MySQLDataSourceService
{
    /**
     * 向平台申明所需参数,在DMP数据源与数据集中可配置该参数的具体值。
     * 在接口调用时,会携带具体值。获取方式:$builder->getBizParamValue('parameter_name')
     * @return array
     */
    public function getBizParams()
    {
        $proCode = new BizParameter();
        $proCode->range = 'datasource';     //指定参数在定义数据源时进行配置
        $proCode->type = 'sys';             //DMP内置参数值
        $proCode->key = 'project_code'; //DMP平台内置租户代码
        $proCode->name = 'proj_code';       //参数名称
        $proCode->description = '租户代码'; //参数描述
        $proCode->required = true;          //设置为必须参数
        $proCode->values = [];              //可选值

        $userID = new BizParameter();
        $userID->range = 'dataset';
        $userID->type = 'query';
        $userID->key = 'user_id'; //DMP报告请求参数
        $userID->name = 'user_id';
        $userID->description = '登录用户ID';
        $userID->required = true;
        $userID->values = [];

        return [$proCode, $userID];
    }

    /***
     * 获取数据
     * ps:无任何的自定义业务需求,则不必重写该函数
     * @param DataQueryBuilder $builder
     * @return array
     * @throws \dmp\exception\ArgumentException
     */
    public function getData(DataQueryBuilder $builder)
    {
        //获取user_id
        $userID = $builder->getBizParamValue('user_id');
        $tableNames = $builder->query_structure->getObjectNames();
        foreach ($tableNames as $tableName) {
            //增加用户过滤
            $builder->query_structure->where[] = new Condition([
                'logical_relation' => empty($builder->query_structure->where) ? '' : 'AND',
                'left' => new Property([
                    'obj_name' => $tableName,
                    'prop_name' => 'user_id',
                ]),
                'operator' => '=',
                'right' => new Property([
                    'value' => $userID
                ]),
            ]);
        }
        return parent::getData($builder);
    }

    /**
     * 获取MySQL连接信息
     * @param QueryBuilderBase $builder
     * @return Connection
     */
    public function getDataSource(QueryBuilderBase $builder)
    {
        /*
         多租户需求:可以根据业务中定义的租户代码,返回不同的连接
         */
        $connParams = [
            'host' => '127.0.0.1',
            'db' => 'database',
            'user' => 'uid',
            'password' => 'pwd'
        ];
        return new Connection($connParams);
    }
}

API数据集查询结构

  "biz_params": {
    "k": "v"
  },
  "query_structure": {
    "select": [
      {
        "obj_name": "表名",
        "prop_name": "列名",
        "alias": "别名",
        "func": "函数名称,如MAX,MIN,AVG。设置后,函数内所需的列与值,均配置在props中(列的集合)",
        "specifier": "修饰符,如DISTINCT",
        "operator": "+|-|*|/|,",
        "value": "固定值,与列名互斥",
        "props": [
          {
            "obj_name": "表名",
            "prop_name": "列名",
            "alias": "别名",
            "func": "函数名称,如MAX,MIN,AVG。设置后,函数内所需的列与值,均配置在props中(列的集合)",
            "props": [

            ],
            "specifier": "修饰符,如DISTINCT",
            "operator": "+|-|*|/|,",
            "value": "固定值,与列名互斥"
          }
        ]
      }
    ],
    "object": [
      {
        "name": "表名",
        "join_type": "left",
        "ref_clause": [
          {
            "logical_relation": "逻辑关系 :AND|OR,没有关联默认为空",
            "left": {
              "obj_name": "表名",
              "prop_name": "列名",
              "alias": "别名",
              "func": "函数名称,如MAX,MIN,AVG。设置后,函数内所需的列与值,均配置在props中(列的集合)",
              "props": [

              ],
              "specifier": "修饰符,如DISTINCT",
              "operator": "+|-|*|/|,",
              "value": "固定值,与列名互斥"
            },
            "operator": "运算符:<|>|=|<>|LIKE",
            "right": {
              "obj_name": "表名",
              "prop_name": "列名",
              "alias": "别名",
              "func": "函数名称,如MAX,MIN,AVG。设置后,函数内所需的列与值,均配置在props中(列的集合)",
              "props": [

              ],
              "specifier": "修饰符,如DISTINCT",
              "operator": "+|-|*|/|,",
              "value": "固定值,与列名互斥"
            },
            "conditions": [
              {
                "logical_relation": "逻辑关系 :AND|OR,没有关联默认为空",
                "left": {
                  "obj_name": "表名",
                  "prop_name": "列名",
                  "alias": "别名",
                  "func": "函数名称,如MAX,MIN,AVG。设置后,函数内所需的列与值,均配置在props中(列的集合)",
                  "props": [

                  ],
                  "specifier": "修饰符,如DISTINCT",
                  "operator": "+|-|*|/|,",
                  "value": "固定值,与列名互斥"
                },
                "operator": "运算符:<|>|=|<>|LIKE",
                "right": {
                  "obj_name": "表名",
                  "prop_name": "列名",
                  "alias": "别名",
                  "func": "函数名称,如MAX,MIN,AVG。设置后,函数内所需的列与值,均配置在props中(列的集合)",
                  "props": [

                  ],
                  "specifier": "修饰符,如DISTINCT",
                  "operator": "+|-|*|/|,",
                  "value": "固定值,与列名互斥"
                },
                "conditions": [

                ]
              }
            ]
          }
        ]
      }
    ],
    "where": [
      {
        "left": {
          "obj_name": "表名",
          "prop_name": "列名",
          "alias": "别名",
          "func": "函数名称,如MAX,MIN,AVG。设置后,函数内所需的列与值,均配置在props中(列的集合)",
          "props": [

          ],
          "specifier": "修饰符,如DISTINCT",
          "operator": "+|-|*|/|,",
          "value": "固定值,与列名互斥"
        },
        "operator": "运算符:<|>|=|<>|LIKE",
        "right": {
          "obj_name": "表名",
          "prop_name": "列名",
          "alias": "别名",
          "func": "函数名称,如MAX,MIN,AVG。设置后,函数内所需的列与值,均配置在props中(列的集合)",
          "props": [

          ],
          "specifier": "修饰符,如DISTINCT",
          "operator": "+|-|*|/|,",
          "value": "固定值,与列名互斥"
        },
        "logical_relation": "逻辑关系 :AND|OR,没有关联默认为空",
        "conditions": [
          {
            "left": {
              "obj_name": "表名",
              "prop_name": "列名",
              "alias": "别名",
              "func": "函数名称,如MAX,MIN,AVG。设置后,函数内所需的列与值,均配置在props中(列的集合)",
              "props": [

              ],
              "specifier": "修饰符,如DISTINCT",
              "operator": "+|-|*|/|,",
              "value": "固定值,与列名互斥"
            },
            "operator": "运算符:<|>|=|<>|LIKE",
            "right": {
              "obj_name": "表名",
              "prop_name": "列名",
              "alias": "别名",
              "func": "函数名称,如MAX,MIN,AVG。设置后,函数内所需的列与值,均配置在props中(列的集合)",
              "props": [

              ],
              "specifier": "修饰符,如DISTINCT",
              "operator": "+|-|*|/|,",
              "value": "固定值,与列名互斥"
            },
            "logical_relation": "逻辑关系 :AND|OR,没有关联默认为空",
            "conditions": [

            ]
          }
        ]
      }
    ],
    "group_by": [
      {
        "obj_name": "表名",
        "prop_name": "列名",
        "alias": "别名",
        "func": "函数名称,如MAX,MIN,AVG。设置后,函数内所需的列与值,均配置在props中(列的集合)",
        "specifier": "修饰符,如DISTINCT",
        "operator": "+|-|*|/|,",
        "value": "固定值,与列名互斥",
        "props": [
          {
            "obj_name": "表名",
            "prop_name": "列名",
            "alias": "别名",
            "func": "函数名称,如MAX,MIN,AVG。设置后,函数内所需的列与值,均配置在props中(列的集合)",
            "props": [

            ],
            "specifier": "修饰符,如DISTINCT",
            "operator": "+|-|*|/|,",
            "value": "固定值,与列名互斥"
          }
        ]
      }
    ],
    "having": [
      {
        "logical_relation": "逻辑关系 :AND|OR,没有关联默认为空",
        "left": {
          "obj_name": "表名",
          "prop_name": "列名",
          "alias": "别名",
          "func": "函数名称,如MAX,MIN,AVG。设置后,函数内所需的列与值,均配置在props中(列的集合)",
          "props": [

          ],
          "specifier": "修饰符,如DISTINCT",
          "operator": "+|-|*|/|,",
          "value": "固定值,与列名互斥"
        },
        "operator": "运算符:<|>|=|<>|LIKE",
        "right": {
          "obj_name": "表名",
          "prop_name": "列名",
          "alias": "别名",
          "func": "函数名称,如MAX,MIN,AVG。设置后,函数内所需的列与值,均配置在props中(列的集合)",
          "props": [

          ],
          "specifier": "修饰符,如DISTINCT",
          "operator": "+|-|*|/|,",
          "value": "固定值,与列名互斥"
        },
        "conditions": [
          {
            "logical_relation": "逻辑关系 :AND|OR,没有关联默认为空",
            "left": {
              "obj_name": "表名",
              "prop_name": "列名",
              "alias": "别名",
              "func": "函数名称,如MAX,MIN,AVG。设置后,函数内所需的列与值,均配置在props中(列的集合)",
              "props": [

              ],
              "specifier": "修饰符,如DISTINCT",
              "operator": "+|-|*|/|,",
              "value": "固定值,与列名互斥"
            },
            "operator": "运算符:<|>|=|<>|LIKE",
            "right": {
              "obj_name": "表名",
              "prop_name": "列名",
              "alias": "别名",
              "func": "函数名称,如MAX,MIN,AVG。设置后,函数内所需的列与值,均配置在props中(列的集合)",
              "props": [

              ],
              "specifier": "修饰符,如DISTINCT",
              "operator": "+|-|*|/|,",
              "value": "固定值,与列名互斥"
            },
            "conditions": [

            ]
          }
        ]
      }
    ],
    "order_by": [
      {
        "obj_name": "表名",
        "prop_name": "列名",
        "alias": "别名",
        "func": "函数名称,如MAX,MIN,AVG。设置后,函数内所需的列与值,均配置在props中(列的集合)",
        "specifier": "修饰符,如DISTINCT",
        "operator": "+|-|*|/|,",
        "value": "固定值,与列名互斥",
        "props": [
          {
            "obj_name": "表名",
            "prop_name": "列名",
            "alias": "别名",
            "func": "函数名称,如MAX,MIN,AVG。设置后,函数内所需的列与值,均配置在props中(列的集合)",
            "props": [

            ],
            "specifier": "修饰符,如DISTINCT",
            "operator": "+|-|*|/|,",
            "value": "固定值,与列名互斥"
          }
        ],
        "method": "DESC"
      }
    ],
    "limit": {
      "offset": 0,
      "row": 10
    }
  }
}

results matching ""

    No results matching ""