README

这是一个简单的工具，可以从JSON Schema生成SQL DDL语句。

安装

$ composer require aaron-lin/json-schema-sql-builder

用法

从JSON Schema构建SELECT语句

例如，JSON Schema如下

{
  "$schema": "https://json-schema.fullstack.org.cn/draft-06/schema#",
  "@table": "products",
  "@id": "id",
  "type": "object",
  "properties": {
    "id": {
      "type": "string"
    },
    "name": {
      "type": "string"
    },
    "_type_name": {
      "type": "string"
    },
    "_weight": {
      "type": "object",
      "properties": {
        "weight": {
          "type": "number"
        },
        "weight_unit": {
          "type": "string",
          "enum": [
            "g",
            "kg"
          ]
        }
      }
    },
    "_bids": {
      "type": "array",
      "@table": "bids",
      "@joinId": "product_id",
      "@id": "id",
      "@orderBy": "time DESC",
      "items": {
        "type": "object",
        "properties": {
          "id": {
            "type": "string"
          },
          "price": {
            "type": "number"
          },
          "time": {
            "type": "string",
            "format": "date-time"
          }
        }
      }
    }
  }
}

使用以下代码执行SQL SELECT查询

use Lin\JsonSchemaSqlBuilder\Storage;
use Lin\JsonSchemaSqlBuilder\SelectSQLBuilder;

$SchemaURI = 'path/to/schema.json#';
$DSN = 'mysql:host=db;dbname=test;charset=utf8mb4';
$DB = new \PDO($DSN, 'test', 'test');

try {
  Storage::SetSchemaFromURI($SchemaURI);
} catch (\Exception $e) {
  echo $e->getMessage();
  exit;
}
Storage::AddSelectExpression($SchemaURI . '#/properties/_type_name', '(SELECT name FROM product_types WHERE product_types.id = products.type_id LIMIT 1)');
Storage::AddSelectExpression($SchemaURI . '#/properties/_weight/properties/weight', 'products.weight');
Storage::AddSelectExpression($SchemaURI . '#/properties/_weight/properties/weight_unit', 'products.weight_unit');
$Builder = new SelectSQLBuilder($SchemaURI, $DB);
$Builder->SetSelectExpressions()
  ->AddWhere("products.keywords like :keywords", ['keywords' => '%apple%'])
  ->AddOrderBy('products.price', 'DESC')
  ->SetLimit(10)
  ->SetOffset(0);
$Result = $Builder->Execute();
echo json_encode($Result, JSON_PRETTY_PRINT);
// [
//   {
//     "id": "1",
//     "name": "Apple",
//     "_type_name": "Fruit",
//     "_weight": {
//       "weight": "100.00",
//       "weight_unit": "g"
//     },
//     "_bids": [
//       {
//         "id": "7",
//         "price": "400",
//         "time": "2018-01-04 00:00:00"
//       },
//       {
//         "id": "5",
//         "price": "300",
//         "time": "2018-01-03 00:00:00"
//       },
//       {
//         "id": "3",
//         "price": "200",
//         "time": "2018-01-02 00:00:00"
//       },
//       {
//         "id": "1",
//         "price": "100",
//         "time": "2018-01-01 00:00:00"
//       }
//     ]
//   }
// ]

如您所见，在初始化JSON schema存储后，您可以添加选择表达式以间接对应属性（直接属性将自动添加到选择表达式，格式为.）。然后，您可以使用SelectSQLBuilder::Build方法构建SQL SELECT语句。更多详情请参考测试脚本，您也可以在测试目录中找到数据库模式和数据。

从JSON Schema构建INSERT/UPDATE语句

例如，JSON Schema如下

{
  "$schema": "https://json-schema.fullstack.org.cn/draft-06/schema#",
  "@table": "products",
  "@id": "id",
  "type": "object",
  "properties": {
    "id": {
      "type": "string"
    },
    "name": {
      "type": "string"
    },
    "_type_name": {
      "type": "string",
      "readonly": true
    },
    "_weight": {
      "type": "object",
      "properties": {
        "weight": {
          "type": "number"
        },
        "weight_unit": {
          "type": "string",
          "enum": [
            "g",
            "kg"
          ]
        }
      }
    },
    "_bids": {
      "type": "array",
      "@table": "bids",
      "@joinId": "product_id",
      "@id": "id",
      "@orderBy": "time DESC",
      "items": {
        "type": "object",
        "properties": {
          "id": {
            "type": "string"
          },
          "price": {
            "type": "number"
          },
          "time": {
            "type": "string",
            "format": "date-time"
          }
        }
      }
    }
  }
}

数据如下

{
  "id": "1",
  "name": "Apple",
  "_type_name": "Fruit",
  "_weight": {
    "weight": "100.00",
    "weight_unit": "g"
  },
  "_bids": [
    {
      "id": "7",
      "price": "400",
      "time": "2018-01-04 00:00:00"
    },
    {
      "id": "5",
      "price": "300",
      "time": "2018-01-03 00:00:00"
    },
    {
      "id": "3",
      "price": "200",
      "time": "2018-01-02 00:00:00"
    },
    {
      "id": "1",
      "price": "100",
      "time": "2018-01-01 00:00:00"
    }
  ]
}

使用以下代码执行SQL INSERT/UPDATE查询

use Lin\JsonSchemaSqlBuilder\Storage;
use Lin\JsonSchemaSqlBuilder\UpsertSQLBuilder;

$SchemaURI = __DIR__ . '/schema.json#';
$DSN = 'mysql:host=db;dbname=test;charset=utf8mb4';
$DB = new \PDO($DSN, 'test', 'test');

try {
  Storage::SetSchemaFromURI($SchemaURI);
} catch (\Exception $e) {
    echo $e->getMessage();
  exit;
}

Storage::AddSelectExpression($SchemaURI . '#/properties/_weight/properties/weight', 'products.weight');
Storage::AddSelectExpression($SchemaURI . '#/properties/_weight/properties/weight_unit', 'products.weight_unit');
$Builder = new UpsertSQLBuilder($SchemaURI, $DB, $Data);
$Builder->SetAssignmentList();
$ResultCount = $Builder->Execute();
echo $ResultCount;
// for every new rows added, the return value is 1,
// for every existing rows updated, the return value is 2.
// data contains 1 row in products, 4 rows in bids,
// therefore, if data are all new rows, the return value is 5,
// or data are all existing rows, the return value is 10

对于每新增的行，UpsertSQLBuilder::Execute的返回值为1，对于每更新的现有行，返回值为2。更多详情请参考测试脚本，您也可以在测试目录中找到数据库模式和数据。