README

中文版读我 / 中文版说明

| 为什么 | 特性 | 需求 | 演示项目 | 安装 | 使用 | 测试 | 许可 | 关于我 |

基于 PDO 的简单 PHP 持久性框架，支持 SQL 映射。

将其视为 MyBatis 的 PHP 轻量级替代实现。

在 PHP 项目中编写业务数据库访问代码时应该很有帮助。

为什么？

当我决定快速构建某物时，PHP 是我的首选。

但数据访问代码每次都让我烦恼，所以我决定开源这个项目。

受一个名为 "IRON" 的框架中的简单数据访问库的启发，该库是我曾经工作过的公司有赞的一个私有库。这是一家很棒的公司，向那里的工程师致敬。

(当然，本项目中的代码没有任何部分来自那个 "IRON" 框架。)

特性

• 一个可视化工具，用于生成数据访问对象类文件和配置文件的代码，查看 iCopyPaste
• 支持简单的条件子语句语法
• 支持 SELECT..IN 查询，PDO 中这一特性较弱
• 轻量级，所有配置都存储在 PHP 原生数组中

需求

• PHP 7.3+
• PHP PDO 扩展以及合适的数据库驱动程序，推荐使用 MySQL 或 Sqlite
• PHP JSON 扩展

演示项目

查看项目中的 MySpot 动作

MySpot-Demo-Application

安装

composer require vimac/myspot

使用

GUI 工具

您可以使用 iCopyPaste 生成基本查询、配置模板、DAO 代码模板的变体。

下载： iCopyPaste 发布页面

初始化

<?php
use MySpot\SqlMapConfig;
use MySpot\SqlMap;

// Initialize PDO first, deal with your database connection
$pdo = new PDO('sqlite::memory:'); 

// Initialize SqlMapConfig
// Specific your SQL map configuration dir
// Like `__DIR__ . '/../config/myspot'`
$sqlMapConfig = new SqlMapConfig('__PATH_TO_YOUR_CONFIGURATION_DIR__', $pdo);

// Optional, you can setup your default map style, _under_score_ to lowerCamelCase is recommended
$sqlMapConfig->setDefaultResultMapStyle(\MySpot\SqlMapStatement::MAP_STYLE_UNDERSCORE_TO_LOWER_CAMELCASE);

// Initialize SqlMap
// If you are using a framework which support Dependency Injection, it is recommend that you put this in SqlMap your container
// You can checkout the demo project and see how it works
$sqlMap = new SqlMap($sqlMapConfig);

配置

配置文件应类似于以下内容

<?php
// Example file name: ${projectDir}/config/myspot/test/post.php
// Note: This file should be put in the directory which the initialize code specific
Use MySpot\SqlMapStatement;
return [
    'select' => [
        'sql' => 'SELECT * FROM `test`.`post`',
        'resultType' => \MyProject\DataObject\Test\PostDO::class, // resultType only available when it's a select query
        'resultMapStyle' => SqlMapStatement::MAP_STYLE_UNDERSCORE_TO_LOWER_CAMELCASE // The statement specific map style
    ],
    'selectById' => [
        'sql' => 'SELECT * FROM `test`.`post` WHERE `id` = :id LIMIT 1',
        'resultType' => \MyProject\DataObject\Test\PostDO::class,
        'resultMapStyle' => SqlMapStatement::MAP_STYLE_UNDERSCORE_TO_LOWER_CAMELCASE
    ],
    'selectByIds' => [
        'sql' => 'SELECT * FROM `test`.`post` WHERE `id` in :id:',
        'resultType' => \MyProject\DataObject\Test\PostDO::class,
        'resultMapStyle' => SqlMapStatement::MAP_STYLE_UNDERSCORE_TO_LOWER_CAMELCASE
    ],
    'selectCountByUid' => [
        'sql' => 'SELECT COUNT(*) FROM `test`.`post` WHERE `uid` = :uid'
    ],
    'selectIdUidTitleSummary' => [
        'sql' => 'SELECT `id`, `uid`, `title`, `summary` FROM `test`.`post` :orderByCreatedAt?{ORDER BY `created_at` ASC}',
        'resultType' => \MyProject\DataObject\Test\PostDO::class,
        'resultMapStyle' => SqlMapStatement::MAP_STYLE_UNDERSCORE_TO_LOWER_CAMELCASE
    ],
    'insert' => [
        'sql' => 'INSERT INTO `test`.`post` #INSERT#'
    ],
    'insertUidTitleSummaryCreatedAt' => [
        'sql' => 'INSERT INTO `test`.`post`(`uid`, `title`, `summary`, `created_at`) VALUES (:uid, :title, :summary, :createdAt)'
    ],
    'updateUidTitleSummaryById' => [
        'sql' => 'UPDATE `test`.`post` SET `uid` = :newUid, `title` = :newTitle, `summary` = :newSummary WHERE `id` = :id'
    ],
    'deleteByUid' => [
        'sql' => 'DELETE FROM `test`.`post` WHERE `uid` IN :uid: LIMIT 1'
    ]
];

特殊语法

MySpot 实现了一个语法解析器，用于将特殊语法编译成实际的 PDO 预处理语句

SELECT...IN 语句

:variable

variable 会被视为一个数组，并编译为 (:variableItem0, :variableItem1, :variableItem2, ...)

条件子语句

:variable?{ 子语句 }

当 variable 等于 TRUE 时，只有 substatement 部分可用

SELECT

常规 SELECT 查询

<?php
// ...

/** 
 * Method 'select' parameters: statementId, [statementParameters]
 * e.g
 * statementId: db.user.selectById
 */
$sqlMapResult = $sqlMap->select('configParentDir.configParentFile.statementId', [
    // 'parameterName' => ['parameterValue', parameterType]
    // parameterType could be omit, which will be default value: MySpot\SqlMapConst::PARAM_STR
    'id' => [1, \MySpot\SqlMapConst::PARAM_INT]
]);

// e.g:
// SQL Template: SELECT * FROM `test`.`post` WHERE `id` = :id
$id = 1;
$sqlMapResult = $sqlMap->select('test.post.selectById', [
    'id' => [$id, \MySpot\SqlMapConst::PARAM_INT]
]);

SELECT..IN 查询

<?php
// In most case, it's no difference than normal SELECT query, except its parameter is an array
// e.g:
// SQL Template: SELECT * FROM `test`.`post` WHERE `id` in :id:
$ids = [1, 2, 3];
$sqlMapResult = $sqlMap->select('test.post.selectByIds', [
    'id' => [$ids, \MySpot\SqlMapConst::PARAM_INT]
]);

UPDATE

<?php
// SQL template: UPDATE `test`.`post` SET `uid` = :newUid, `title` = :newTitle, `summary` = :newSummary WHERE `id` = :id
$sqlMapResult = $sqlMap->update('test.post.updateUidTitleSummaryCreatedAtById', [
    'newUid' => [$newUid, \MySpot\SqlMapConst::PARAM_INT], 
    'newTitle' => [$newTitle, \MySpot\SqlMapConst::PARAM_STR], 
    'newSummary' => [$newSummary, \MySpot\SqlMapConst::PARAM_STR], 
    'newCreatedAt' => [$newCreatedAt, \MySpot\SqlMapConst::PARAM_STR], 
    'id' => [$id, \MySpot\SqlMapConst::PARAM_INT]
]);

INSERT

<?php
// Insert with auto fields name
// SQL template: INSERT INTO `test`.`post` #INSERT#
$data = [
    'uid' => [$uid, \MySpot\SqlMapConst::PARAM_INT],
    'title' => [$title],
    'summary' => [$summary],
    'created_at' => [$createdAt] // Notice the key name should be original field name in the table
];
$sqlMapResult = $sqlMap->insert('test.post.insert', [], $data);

// Insert with named parameter
// SQL Template: INSERT INTO `test`.`post`(`uid`, `title`, `summary`, `created_at`) VALUES (:uid, :title, :summary, :createdAt)
$sqlMapResult = $sqlMap->insert('test.post.insertUidTitleSummaryCreatedAt', [
    'uid' => [$uid, \MySpot\SqlMapConst::PARAM_INT], 
    'title' => [$title, \MySpot\SqlMapConst::PARAM_STR], 
    'summary' => [$summary, \MySpot\SqlMapConst::PARAM_STR], 
    'createdAt' => [$createdAt, \MySpot\SqlMapConst::PARAM_STR]
]);

DELETE

<?php
// SQL Template: DELETE FROM `test`.`post` WHERE `uid` IN :uid: LIMIT 1
$sqlMapResult = $sqlMap->delete('test.post.deleteByUid', [
    'uid' => [$uid, \MySpot\SqlMapConst::PARAM_INT]
]);

条件子语句

<?php
// SQL Template: SELECT `id`, `uid`, `title`, `summary` FROM `test`.`post` :orderByCreatedAt?{ORDER BY`created_at` DESC}

// Equals: SELECT `id`, `uid`, `title`, `summary` FROM `test`.`post` 
$sqlMapResult = $sqlMap->select('test.post.selectIdUidTitleSummary', [
    'orderByCreatedAt' => [false, \MySpot\SqlMapConst::PARAM_BOOL]
]);

// Equals: SELECT `id`, `uid`, `title`, `summary` FROM `test`.`post` ORDER BY `created_at` DESC
$sqlMapResult = $sqlMap->select('test.post.selectIdUidTitleSummary', [
    'orderByCreatedAt' => [true, \MySpot\SqlMapConst::PARAM_BOOL]
]);

获取结果

<?php
// All of the result will be combined into a class instance of `MySpot\SqlMapResult`

// Fetch an array of object or array of array depends on your configuration
$sqlMapResult->fetchAll();

// Fetch first object or array depends on your configuration
$sqlMapResult->fetch();

// Traverse all of the fetched data
while ($result = $sqlMapResult->fetch()) {
    // Do something
}

// Fetch the first column of first row, it's useful for SELECT COUNT query
$sqlMapResult->fetchColumn();

// Fetch the specific column of first row
$sqlMapResult->fetchColumn(3);

// Fetch the last insert Id when you inserted new row
$sqlMapResult->getLastInsertId();

// Fetch the affected lines when you updated or deleted something
$sqlMapResult->getAffectedLines();

// Fetch the execute result in boolean value
$sqlMapResult->getExecutedResult();

// Get the bound PDOStatement
$sqlMapResult->getStatement();

测试

# Simple run the command in project root dir
composer test

许可

MIT 许可

关于我

vifix.cn