nagyatka/pandabase

MySQL 数据库抽象层

维护者

详细信息

github.com/nagyatka/pandabase

主页

源代码

问题

安装: 291

依赖项: 5

建议者: 0

安全: 0

星标: 2

关注者: 4

分支: 1

开放问题: 1

v0.21.3 2021-01-05 21:21 UTC

Requires

  • ext-pdo: *

Requires (Dev)

Apache-2.0 d625bed3409f1db9198303f68d61595066c1a1b9

  • Attila Nagy <anagy.woop@hit.bme.hu>

ormabstractionmysqlpdo

This package is auto-updated.

Last update: 2024-09-06 18:49:52 UTC

README

安装

$ composer require nagyatka/pandabase

我们建议使用 v0.20.0 以上的版本，因为 API 和性能有重大变化。

如何使用 ConnectionManager

获取 ConnectionManager 实例

您可以通过 getInstance() 方法全局访问 ConnectionManager 单例实例。

$connectionManager = ConnectionManager::getInstance();

将连接添加到管理对象

您可以在 Pandabase 中轻松设置新的数据库连接。

$connectionManager->initializeConnection([
    "name"      =>  "test_connection",  // Connection's name.
    "driver"    =>  "mysql",            // Same as PDO parameter
    "dbname"    =>  "test_dbname",      // Same as PDO parameter
    "host"      =>  "127.0.0.1",        // Same as PDO parameter
    "user"      =>  "root",             // Same as PDO parameter
    "password"  =>  ""                  // Same as PDO parameter
    "attributes"=>  [
        attributeName => value,
        ...
    ]                                   // Optional, PDO attributes
]);

将更多连接添加到管理对象

ConnectionManager 能够同时处理多个连接。您可以通过名称参数区分连接，例如，在以下示例中，您可以使用 "test_connection1" 和 "test_connection2"。

$connectionManager->initializeConnections(
    [
        [
            "name"      =>  "test_connection1", // Connection's name.
            "driver"    =>  "mysql",            // Same as PDO parameter
            "dbname"    =>  "test_dbname1",     // Same as PDO parameter
            "host"      =>  "127.0.0.1",        // Same as PDO parameter
            "user"      =>  "root",             // Same as PDO parameter
            "password"  =>  ""                  // Same as PDO parameter
        ],
        [
            "name"      =>  "test_connection2", // Connection's name.
            "driver"    =>  "mysql",            // Same as PDO parameter
            "dbname"    =>  "test_dbname2",     // Same as PDO parameter
            "host"      =>  "127.0.0.1",        // Same as PDO parameter
            "user"      =>  "root",             // Same as PDO parameter
            "password"  =>  ""                  // Same as PDO parameter
        ],

    ]
);

获取连接

如果留空名称参数，getConnection 方法将返回默认连接。默认连接将是首先设置的连接。

$connection = $connectionManager->getConnection();

通过名称获取连接

$connection = $connectionManager->getConnection("test_connection2");

通过名称设置默认连接

// Set the 'test_connection2' Connection instance as the default
$connectionManager->setDefault("test_connection2");

// Returns with the instance of 'test_connection2' if exists
$connection = $connectionManager->getConnection();

使用 ConnectionManager 执行查询

// Fetch a result row as an associative array
$queryResult = ConnectionManager::fetchAssoc("SELECT * FROM table1 WHERE table_id = :_id", [
    "_id" => 11
]); 

// Fetch result from default connection
$queryResult1 = ConnectionManager::fetchAll("SELECT * FROM table1");

// Fetch result from default connection with parameters
$queryResult2 = ConnectionManager::fetchAll("SELECT * FROM table1 WHERE store_date > :actual_date",[
    "actual_date" => date("Y-m-d H:i:s")
]);

// Fetch result from specified connection (without parameters)
$queryResult3 = ConnectionManager::fetchAll("SELECT * FROM table1",[],"test_connection2");

如何使用 Connection

Connection 是一个 PDO 包装器（所有 PDO 函数均可调用）并提供更好的可用性而修改的 fetchAssoc 和 fetchAll 方法。尽管 ConnectionManager 实例为 Connection 实例的函数提供了包装函数，但我们建议使用这些包装函数而不是直接调用它们。

获取连接

$connection = $connectionManager->getConnection();

将结果行作为关联数组获取

$result = $connection->fetchAssoc("SELECT * FROM table1 WHERE id = :id",["id" => $id]);

返回包含所有结果集行的关联数组。

$result = $connection->fetchAll("SELECT * FROM table1",[]);

根据数据库模式创建类

您可以根据数据库中的表创建类。为此，您只需将您的类从 SimpleRecord 或 HistoryableRecord 继承并注册到指定的连接。

SimpleRecord

实现 SimpleRecord 类

假设我们有一个名为 transactions 的 MySQL 表，它有一个主键。

CREATE TABLE `database_name`.`transactions` (
	`transaction_id` int(11) NOT NULL AUTO_INCREMENT,
	`transaction_value` int(11),
	`user_id` int(11),
	`store_date` datetime,
	PRIMARY KEY (`transaction_id`)
) ENGINE=`InnoDB` COMMENT='';

实现 Transaction 类

class Transaction extends SimpleRecord {

}

在初始化连接时，您必须以下列方式将 Table 对象（这是一个表描述符类）添加到指定的 Connection 实例中。

$connectionManager->initializeConnection([
    "name"      =>  "test_connection",  // Connection's name.
    "driver"    =>  "mysql",            // Same as PDO parameter
    "dbname"    =>  "database_name",    // Same as PDO parameter
    "host"      =>  "127.0.0.1",        // Same as PDO parameter
    "user"      =>  "root",             // Same as PDO parameter
    "password"  =>  ""                  // Same as PDO parameter
    "attributes"=>  [
        attributeName => value,
        ...
    ],                                  // Optional, PDO attributes
    "tables"    =>  [
        Transaction::class  => new Table([
            Table::TABLE_NAME => "transactions",
            Table::TABLE_ID   => "transaction_id",
        ]),
        ...
    ]
]);

就是这样！现在您可以从表中创建、更新和删除记录。

// Create a new empty record (if your table scheme allows it)
$emptyRecord = new Transaction();
// Create a new record with values
$newRecord = new Transaction([
            "transaction_value"     =>  5000,
            "user_id"               =>  1234,
            "store_date"            =>  date('Y-m-d H:i:s')
]);
// To create new records in table you have to call ConnectionManager's persist function
ConnectionManager::persist($emptyRecord);
ConnectionManager::persist($newRecord);

// An other option is to use persistAll function
ConnectionManager::persistAll([
    $emptyRecord,
    $newRecord
]);

// Now $emptyRecord and $newRecord have transaction_id attribute
echo $emptyRecord["transaction_id"]." ".$newRecord["transaction_id"]."\n";



// Load record
$transaction = new Transaction($transactionId);
echo $transation->get("store_date").": ".$transaction["transaction_value"]; // You can use object as an array

// Load multiple record from transaction table (get all transaction of an user)
$transactions = ConnectionManager::getInstanceRecords(
    Transaction::class,
    "SELECT * FROM transactions WHERE user_id = :user_id",
    [
        "user_id"   =>  1234
    ]
);



// Update record
$transaction = new Transaction($transactionId);
$transation->set("transaction_value",4900);
$transation["store_date"] = date('Y-m-d H:i:s'); //You can use object as an array
ConnectionManager::persist($transation);



// Remove record
$transaction = new Transaction($transactionId);
$transation->remove();

HistoryableRecord

HistoryableRecord 与 SimpleRecord 具有相同的功能，但它还存储记录的先前状态。

实现 HistoryableRecord 类

假设我们有一个名为 transactions 的 MySQL 表，该表具有以下列（所有列均为必填项）

  • sequence_id（主键）
  • id（记录标识符，您可以在代码中使用它作为 ID）
  • record_status（0|1 -> 不活动|活动）
  • history_from（datetime）
  • history_to（datetime）
CREATE TABLE `database_name`.`orders` (
    `order_sequence_id` int(11) NOT NULL AUTO_INCREMENT,
	`order_id` int(11),
	`record_status` int(1),
	`history_from` datetime,
	`history_to` datetime,
	`order_status` int(11),
	`user_id` int(11),
	`store_date` datetime,
	PRIMARY KEY (`order_sequence_id`)
) ENGINE=`InnoDB` COMMENT='';

实现 Order 类

class Order extends HistoryableRecord {
    const Pending       = 0;	
    const Processing    = 1;	
    const Completed	    = 2;
    const Declined      = 3;	
    const Cancelled     = 4;
    
    /**
     * Constructor
     */
    public function __construct($parameters) {
        $parameters["order_status"] = Order::Pending;
        parent::__construct($parameters);
    }
    
    ...
}

在初始化连接时，您必须以下列方式将 Table 对象（这是一个表描述符类）添加到指定的 Connection 实例中。

$connectionManager->initializeConnection([
    "name"      =>  "test_connection",  // Connection's name.
    "driver"    =>  "mysql",            // Same as PDO parameter
    "dbname"    =>  "database_name",    // Same as PDO parameter
    "host"      =>  "127.0.0.1",        // Same as PDO parameter
    "user"      =>  "root",             // Same as PDO parameter
    "password"  =>  ""                  // Same as PDO parameter
    "attributes"=>  [
        attributeName => value,
        ...
    ],                                  // Optional, PDO attributes
    "tables"    =>  [
        Order::class  => new Table([
            Table::TABLE_NAME   => "orders",
            Table::TABLE_ID     => "order_id",
            Table::TABLE_SEQ_ID => "order_sequence_id"
        ]),
        ...
    ]
]);

现在您可以使用 HistoryableRecord 作为 SimpleRecord，但您还可以获取实例的历史信息。

    $order = new Order($order_id);
    
    // Get full history
    $orderHistory = $order->getHistory();
    
    // You can also specify a date interval
    $orderHistory = $order->getHistoryBetweenDates("2017-01-05","2017-01-08");

延迟属性

有时您必须在表中存储外键以表示不同对象之间的连接。在没有延迟属性加载的情况下，您可以这样加载对象：

    $transaction = new Transaction($transactionId);
    $order = new Order($transaction->get("order_id")); // We suppose that a transaction table also stores a valid order_id

或者如果您想提供一个类方法

    class Transaction extends SimpleRecord {
        // ...
        
        /** @var Order */
        private $order;
        
        // ...
        
        /** @return Order */
        public function getOrder() {
            if($this->order == null) {
                $this->order = new Order($this->get("order_id"));
            }
            return $this->order;
        }
    }

而不是这样，您可以使用 LazyAttribute 以快速简便的方式实现此类连接。

首先您必须扩展您的表描述。在我们的示例中，我们希望为交易记录存储一个 'order_id' 并通过 'order' 键访问适当的 Order 实例

$connectionManager->initializeConnection([
    "name"      =>  "test_connection",  // Connection's name.
    "driver"    =>  "mysql",            // Same as PDO parameter
    "dbname"    =>  "database_name",    // Same as PDO parameter
    "host"      =>  "127.0.0.1",        // Same as PDO parameter
    "user"      =>  "root",             // Same as PDO parameter
    "password"  =>  ""                  // Same as PDO parameter
    "attributes"=>  [
        attributeName => value,
        ...
    ],                                  // Optional, PDO attributes
    "tables"    =>  [
        Order::class  => new Table([
            Table::TABLE_NAME   => "orders",
            Table::TABLE_ID     => "order_id",
            Table::TABLE_SEQ_ID => "order_sequence_id"
        ]),
        Transaction::class  => new Table([
            Table::TABLE_NAME       => "transactions",
            Table::TABLE_ID         => "transaction_id",
            Table::LAZY_ATTRIBUTES  => [
                "order" => new LazyAttribute("order_id",Order::class)
            ]
        ]),
        ...
    ]
]);

（我们假设您还扩展了 mysql 表声明并添加了新的 'order_id' 列。）

现在您可以使用以下方式使用 Transaction 实例：

// Load Transaction instance from db
$transaction = new Transaction($transactionId);

echo $transation->get("store_date").": ".$transaction["transaction_value"]; // You can use object as an array

/** @var Order $transactionOrder */
$transactionOrder = $transaction["order"]; // Return with an Order instance
$transactionOrderHistory = $transactionOrder->getHistory();

#### TODO：访问管理

许可证

PandaBase 在 Apache 2.0 许可证下授权。